UPDATE database based on different values

[jarv]

hi, the following SQL:

UPDATE `customer` SET `ServiceDate` = DATE_ADD('1900-01-01', INTERVAL 40812 DAY)

updates all records in my table at the column ServiceDate with a date 40812 days after 1900-01-01

 

My problem is is that each of the values in the ServiceDate column are not 40812, they are all different. 40812, 40965, 40723 and so on...

 

Please help

[kickstart]

Hi

 

How do you know which number of days to add to which row?

 

I would guess that you have them stored on a different table. If so you probably want to do a JOIN within the UPDATE statement.

 

Something like:-

 

UPDATE FROM customer AS c 
LEFT JOIN someOtherTable as s 
ON c.blah = s.blah
SET c.ServiceDate` = DATE_ADD('1900-01-01', INTERVAL s.SomeDayCount DAY) 

 

All the best

 

Keith