SQL: How to self-reference a column in update stat

问题:

I have two tables with these columns:

Users: user, class, grade, location.

Classes: class, location

The class column of the Users table references the same colmn in Classes table.

I want to update all rows in the Users table such that each row of the Users table's "location" column is equal to the location of the class.

So i have a row with values: Mike, Math, A+, New York

And the corresponding row in Classes table for Math is: Math, Chicago

I want the user table's row to become Mike, Math, A+, Chicago.

Thanks

回答1:

update users a
    set a.location =
     (select b.location 
      from classes b
      where b.class = a.class) 

回答2:

update
  users
set
  users.location = classes.location
from
  classes
where
  classes.class = users.class

回答3:

How about this:

UPDATE U 
SET U.Location = C.Location
FROM Users AS U
INNER JOIN Classes AS C ON C.Class = U.Class
                AND C.Location != U.Location
 ;