Clever Geek Handbook

Mysql - update foreign key with value

I have a date, year, month, day where the "year, month, day" columns are foreign key to other tables I want each parent to get id (year (date of birth)) as the value of the year column and the same for month and day columns.

How do 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 this raises "ERROR 1093 (HY000): you cannot specify the" member "of the target table to update in the FROM clause"

Thank you in advance

+2


source to share


2 answers


You don't want to select from a table members

in a 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?

0


source 


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;


the same thing happens during the month and day.

-1


source






More articles:


All Articles