Update MySQL Date field with single INT from another field?

For simplicity sake, I have two fields within a table:

• Date 1 (YYYY-MM-DD format)
• Day (single or two digit day format, 1-31)

I want to be able to update Date 1 using the value within Day but I DO NOT want to make multiple calls to do so (first a select, fetch results, then update with the result from the same table).

ultimately, the 'design' of my call (which does not work) would be:

UPDATE table SET Date 1 = DATE(Y-(M+1)-(value of Day));

or in php:

date("Y-m-d", mktime(0,0,0,date('m')+1, VALUE(Day), date('Y')));

is this possible?

UPDATE

==

While I have been able to utilize some of the code below, I am not sure MYSQL is 'smart' enough to run the calculation as I have it. My new code is:

UPDATE table SET Date 1= CONCAT(YEAR(CURDATE()),'-',MONTH(ADDDATE(CURDATE(), INTERVAL 1 MONTH)),'-',Day1)

While this returns the correct 'new month' and 'new day', the year will be wrong WHEN the current month is December.

For example: If the current date is 2010-12-02. The preferred data in the Day field is 12. Once our script has processed, the Date 1 field should be updated to 2011-01-12 but in the code above it will only output to开发者_开发百科 2010-01-12.

---

not tested, but i think what you're missing is CONCAT:

UPDATE table SET datefield = CONCAT(YEAR(datefield),'-',MONTH(datefield),'-',dayfield);

after rereading you questioon, it sounds like you want to add the days, that would be like this (not tested, too - take a look at DATE_ADD and INTERVAL):

UPDATE table SET datefield = DAT_ADD(datefield, INTERVAL dayfield DAYS);