我正在用JOIN执行SQL UPDATE,但是该JOIN可以匹配多个值。假设我们有以下表格:
Table_1 Table_2 col_a | col_b col_a | col_b --------------- --------------- 1 | A 1 | X 2 | B 1 | Y 3 | C 3 | Z
然后执行以下查询:
UPDATE t1 SET t1.col_b = t2.col_b FROM Table_1 t1 JOIN Table_2 t2 ON t1.col_a = t2.col_a;
结果如下:
Table_1 Table_2 col_a | col_b col_a | col_b --------------- --------------- 1 | X 1 | X 2 | B 1 | Y 3 | Z 3 | Z
我需要做的是Table_1使用最后匹配的值更新;因此,在这种情况下,我将需要以下结果:
Table_1
Table_1 Table_2 col_a | col_b col_a | col_b --------------- --------------- 1 | Y 1 | X 2 | B 1 | Y 3 | Z 3 | Z
如果您可以定义Table_2中记录的顺序(最后是什么意思?),则可以使用窗口函数来过滤Table_2,以仅包括与之匹配的每组记录的最后一条记录:
UPDATE t1 SET t1.col_b = t2.col_b FROM Table_1 t1 JOIN (SELECT col_a, col_b, ROW_NUMBER() OVER (PARTITION BY col_a ORDER BY <order by field list goes here> DESC) AS RNo FROM Table_2) t2 ON t1.col_a = t2.col_a AND t2.RNo=1;
在特殊情况下,order by字段是col_b,那么您可以简单地使用(这适用于所有版本的SQL Server):
UPDATE t1 SET t1.col_b = t2.col_b FROM Table_1 t1 JOIN (SELECT col_a, MAX(col_b) AS col_b FROM Table_2 GROUP BY col_a) t2 ON t1.col_a = t2.col_a;