SQL:如何在更新语句中自引用列? [英] SQL: How to self-reference a column in update statements?

问题描述

我有两个包含这些列的表:

I have two tables with these columns:

用户:用户、班级、年级、位置.

Users: user, class, grade, location.

课程:班级、地点

Users 表的 class 列引用 Classes 表中的相同列.

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.

所以我有一排值:Mike，数学，A+，纽约

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

数学类表中对应的行是:数学，芝加哥

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

我希望用户表的行变成Mike，数学，A+，芝加哥.

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

谢谢

推荐答案

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

这篇关于SQL:如何在更新语句中自引用列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！