CODEWITHSUNDEEP
mysql
ACs
ACs

Reputation: 1445

UPDATE a field in each row depending on other field with MySQL

I have a MySQL table where I have a year and a month column beside other columns. I have to replace these 2 INT type columns with 1 DATE type column, but I have to keep and convert old record values of course. The day always will be 01 on old records.

For example: this is what I have now

[project_planned_cost] 
ppc_id | year | month |
-----------------------
0      | 2012 |  5
1      | 2013 |  11    
2      | 2008 |  2

And this is what I want to achieve:

[project_planned_cost] 
ppc_id | date 
--------------
0      | 2012-05-01
1      | 2013-11-01   
2      | 2008-02-01

I have figured out how to generate the date but I dont know how to apply it to each row:

ALTER TABLE project_planned_cost ADD COLUMN `date` DATE;

SELECT 
    CONCAT(`year`, '-', LPAD(`month`, 2, '0'), '-', '01') AS 'date'
FROM project_planned_costs

I'd be thankful if you could suggest me something

Upvotes: 0

Views: 84

Answers (2)

Ezhil
Ezhil

Reputation: 1006

    probably your looking for like this

    //Create the column:
    alter project_planned_cost add column date varchar(50);
    // Update the current values
    update project_planned_cost set date = concat(year, ' - ', month, '- ', '01');

    Hope this link help you more
    http://stackoverflow.com/questions/5774532/mysql-combine-two-columns-and-add-into-a-
    new-column

Upvotes: 0

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

ALTER TABLE project_planned_cost ADD COLUMN `date` DATE;

update project_planned_cost  set date =
    CONCAT(`year`, '-', LPAD(`month`, 2, '0'), '-', '01') ;

Upvotes: 2

Related Questions