sql - mysql - update foreign key with a value -

I have a birthday, year, month, day column where the column "year, month, day" foreigners of other tables The key is that I want to get the same value for the column of the year column and the month and day column for each birthdate ID (year (date of birth)).

How can I do this in MySQL?

I tried this solution:

  updated member set year = (All_years.id Select All_years, members where All_years.fromY = year (member.birthdate) ) Where id = 30,471;  

But the reason for this is "Error 1093 (HY 1000): You can not specify the target table 'Member' for updates in the FRM section."

Thanks in advance

You do not want to select from the members table in the subquery Use the table that you are updating.

  Member's SET year UPDATE = (SELECT year from all_years WHERE from Y = year (member birthplace)) WHERE id = 30471;  

Is there any reason that year / month / date are foreign keys though?