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

2022-01-17 00:00:00 join sql-update sql sql-server

我正在使用 JOIN 执行 SQL UPDATE，但该 JOIN 可以匹配多个值.假设我们有以下表格:

I'm performing an SQL UPDATE with JOIN, but that JOIN can match more than one value. Let's say we have the following tables:

    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；所以在这种情况下，我需要这个结果:

What I need to do is to update the Table_1 with the last value matched; so in this case, I would need this result:

    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 只包含最后一个每组匹配记录的记录:

Provided you have a way to define the order of records in Table_2 (what does last mean?) you can use window functions to filter Table_2 to only include the last record of each group of records that match:

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):

In the special case that the order by field is col_b then you can simply use (this works on all versions of 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;

相关文章