Rearranging my DB. (Simple question for some…)

[DBookatay]

I first created my database several years ago, and have since been trying to make changes to it that makes it "proper."

I have 3 char(2) rows: sold_date1, sold_date2 and sold_date3, with values such as "10", "12", "09".

 

How do I take the values from those rows and convert and update them so that they get inserted into a new row "sold_date" with the "date" type?

 

Example "2009-10-12"

 

[coupe-r]

Im not really sure what you are trying to do but?

 

$newdate = $row['sold_date3'].'-'.$row['sold_date2'].'-'.$row['sold_date1'];

 

year-mon-day

 

Am I close to what you want?

[DBookatay]

I want the SQL code that will pull the values from sold_date1, sold_date2, and sold_date3 and put them combined into "sold_date".

Then I can delete the rows and content of sold_date1, sold_date2, and sold_date3.

 

[PFMaBiSmAd]

You would form a single UPDATE query * that uses the mysql CONCAT() function to take the three existing column values and produce the value that you set the new column to.

 

* An update query without a WHERE clause will match all the rows in your table and update every row by executing the query once.

 

[PFMaBiSmAd]

The query would look something like (test it first on a sample of your data to be sure) -

UPDATE your_table SET sold_date = CONCAT('20',sold_date3,'-',sold_date1,'-',sold_date2);

[DBookatay]

The query would look something like (test it first on a sample of your data to be sure) -

UPDATE your_table SET sold_date = CONCAT('20',sold_date3,'-',sold_date1,'-',sold_date2);

 

2 follow up questions, and forgive me because I'm still learning.

    1. This database was started in 1998, so how do I add

CONCAT('19',sold_date3,'-',sold_date1,'-',sold_date2);

if it's a 1998 or 1999 and then a

CONCAT('20',sold_date3,'-',sold_date1,'-',sold_date2);

if it's year 2000 and up.

    2. If there already is the proper date in the "sold_date" field, how do I exclude those. Meaning I only want to alter the rows that have "sold_date" as "0000-00-00" AND data in the "sold_date1", "sold_date2" and "sold_date3" fields.

[PFMaBiSmAd]

Untested, but should work -

UPDATE your_table SET sold_date = CONCAT(IF(sold_date3 IN ('98','99'),'19','20'),sold_date3,'-',sold_date1,'-',sold_date2) WHERE sold_date != '0000-00-00' AND sold_date1 !='' AND sold_date2 != '' AND sold_date3 != '';

 

Edit: I just tried that query and the sold_date != '0000-00-00' logic is backwards. It should be sold_date = '0000-00-00'

[fenway]

You can actually use "0" as a shorthand for a zeroed-out date -- comes in handy if you ever change to a DATETIME later.