小编典典

使用与两个或多个值匹配的JOIN进行SQL UPDATE

sql

我正在用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_2
 col_a | col_b        col_a | col_b
---------------      ---------------
   1   |   Y           1    |    X    
   2   |   B           1    |    Y   
   3   |   Z           3    |    Z

阅读 201

收藏
2021-04-14

共1个答案

小编典典

如果您可以定义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;
2021-04-14