Sql update multiple rows with multi join subtask

Question

This is the updated part 2 of the question I asked earlier. I am trying to do the next update, but this request is actually doing nothing.

UPDATE u SET graduation_class_id = gc.graduation_class_id FROM [user] u JOIN graduation_class gc ON u.graduation_class_id = gc.graduation_class_id JOIN graduation_term gt ON gt.graduation_year_id = gc.graduation_year_id TABLE SCHEMA **user user_id graduation_class_id **graduation_class graduation_class_id graduation_year_id **graduation_term graduation_term_id graduation_year_id 

The goal is to get the corresponding graduation_class_id value in the user table. I was told that this will not work, because a match will not be found if the user does not already have the corresponding gradation_class_id. This is a problem because I'm trying to actually get the right one there!

Answer 1

This idea is, in principle, doomed to failure. You are trying to force the SQL server to associate the grading class with the user, even though the SQL server has no information about which grading class should be associated with the user. If you don’t have any data anywhere (the user, in the grading class, in some other table) that associates user_id with the term, class or year of graduation (or someone manually tells SQL what data matches) , SQL cannot magically make this work for you.