Search code examples
mysqlsqlmysql-error-1093

mysql - update foreign key with a value

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

Solution

  • 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?