sql update multiple rows with subselect

sql, sql-update, subquery

Solution

Several databases support the `update ... from` syntax, which is fairly clear:

UPDATE  mba
SET     graduation_date = gt.graduation_date
FROM    user_education_mba_school mba
JOIN    graduation_term gt
ON      gt.graduation_term_id = mba.graduation_term_id

If your database doesn't, please clarify which database you are using.

Problem

I am trying to copy the corresponding graduation_date values from the graduation_term table into the rows in the user_education_mba_school table that have the matching graduation_term_id. Here is my nonworking query ``` TABLE DEFS **user_education_mba_school school_id graduation_date graduation_term_id **graduation_term graduation_term_id graduation_year_id graduation_date **graduation_class graduation_class_id graduation_year_id **user user_id graduation_class_id ``` -- Multi-JOIN not working... ``` UPDATE u SET graduation_class_id = gc.graduation_class_id FROM [user] u JOIN user_education_mba_school mba ON mba.user_id = u.user_id JOIN graduation_term gt ON mba.graduation_term_id = gt.graduation_term_id JOIN graduation_class gc ON gt.graduation_year_id = gc.graduation_year_id ```

Original source