Sql update multiple rows with subquery

Question

I am trying to copy the appropriate gradation_date values ​​from the graduation_term table to the rows in the user_education_mba_school table that have the corresponding gradation_term_id. Here is my non-working request

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 does not work ...

 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 

Answer 1

Several databases support the syntax update ... from , which is pretty 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 does not work, please check which database you are using.

Answer 2

This will not work because you set the whole set of subquery results

 UPDATE user_education_mba_school SET graduation_date = ( SELECT gt.graduation_date FROM user_education_mba_school mba, graduation_term gt WHERE gt.graduation_term_id = mba.graduation_term_id ) 

This should work (not verified)

 UPDATE user_education_mba_school mba SET graduation_date = ( SELECT gt.graduation_date FROM graduation_term gt WHERE gt.graduation_term_id = mba.graduation_term_id ) 

Answer 3

 UPDATE mba SET graduation_date = gt.graduation_date, mba_graduation_year_id = gt.graduation_year_id FROM user_education_mba_school mba JOIN [sueldovista].dbo.graduation_term gt ON gt.graduation_term_id = mba.graduation_term_id 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 [sueldovista].dbo.graduation_term gt ON mba.graduation_term_id = gt.graduation_term_id JOIN [sueldovista].dbo.graduation_class gc ON gt.graduation_year_id = gc.graduation_year_id WHERE gc.site_instance_id = 1 

Answer 4

ORACLE:

PRODUCT UPDATE a SET (name, account) = (SELECT name, account FROM STEGING b WHERE a.ID = b.ID);