mysql - update foreign key with a value

Question

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.

How can I do this in MySQL?

i tried this solution:

update member set year=(select All_years.id from All_years,member where All_years.fromY=year(member.birthdate)) where id=30471;

but it cause " ERROR 1093 (HY000): You can't specify target table 'member' for update in FROM clause "

Thanks in advance

Answer 1

You don't want to select from the members table in the subquery. Use the table you are updating instead.

UPDATE member
SET year=(
   SELECT id FROM all_years
   WHERE fromY=year(member.birthdate)
)
WHERE id=30471;

Is there a reason why year/month/date are foreign keys though?

Answer 2

SELECT birthdate FROM member INTO @myBirthdate;

update member set year=(select All_years.id from All_years,member where All_years.fromY=year(@myBirthdate)) where id=30471;

same goes for month and day.