sql update multiple rows with subselect

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

​x
 
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

 
UPDATE  uSET     graduation_class_id = gc.graduation_class_idFROM    [user] uJOIN    user_education_mba_school mbaON      mba.user_id = u.user_idJOIN    graduation_term gtON      mba.graduation_term_id = gt.graduation_term_idJOIN    graduation_class gcON      gt.graduation_year_id = gc.graduation_year_id​

Answer

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

 
UPDATE  mbaSET     graduation_date = gt.graduation_dateFROM    user_education_mba_school mbaJOIN    graduation_term gtON      gt.graduation_term_id = mba.graduation_term_id​

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

Advertisement

Answer