title: Update MySQL Date field with single INT from another field?
body: <p>For simplicity sake, I have two fields within a table:</p>
  
  <ul>
  <li>Date 1 (YYYY-MM-DD format)</li>
  <li>Day (single or two digit day format, 1-31)</li>
  </ul>
  
  <p>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).</p>
  
  <p>ultimately, the 'design' of my call (which does not work) would be:</p>
  
  <blockquote>
    <p>UPDATE table SET Date 1 =
    DATE(Y-(M+1)-(value of Day));</p>
  </blockquote>
  
  <p>or in php:</p>
  
  <pre><code>date("Y-m-d", mktime(0,0,0,date('m')+1, VALUE(Day), date('Y')));
  </code></pre>
  
  <p>is this possible?</p>
  
  <p>UPDATE</p>
  
  <p>==</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>UPDATE    table SET   Date 1=
    CONCAT(YEAR(CURDATE()),'-',MONTH(ADDDATE(CURDATE(),
    INTERVAL 1 MONTH)),'-',Day1)</p>
  </blockquote>
  
  <p>While this returns the correct 'new month' and 'new day', the year will be wrong WHEN the current month is December.</p>
  
  <p>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.</p>