title: mysql - update foreign key with a value
body: <p>I have a birthdate, year, month, day columns where columns "year,month,day" are foreign key to other tables
  What I want to do is for each birthdate get id(year(birthdate)) to be the value of year column and the same thing for month and day columns.</p>
  
  <p>How can I do this in MySQL?</p>
  
  <p>i tried this solution:</p>
  
  <pre><code>update member set year=(select All_years.id from All_years,member where All_years.fromY=year(member.birthdate)) where id=30471;
  </code></pre>
  
  <p>but it cause " ERROR 1093 (HY000): You can't specify target table 'member' for update in FROM clause "</p>
  
  <p>Thanks in advance</p>