Sql 用相同值更新行

发布于03月20日

我们有两张桌子:

Table A (ID, NAME)
1, 'NAME1'
2, 'NAME1'
3, 'NAME2'
4, 'NAME2'

Table B (ID, NAME)
NULL, 'NAME1'
NULL, 'NAME1'
NULL, 'NAME2'
NULL, 'NAME2'

如何用表A的ID更新表B，而不会出现单行子查询错误？

传统更新不起作用，单行子查询错误.

推荐答案

您可以使用ROW_NUMBER分析函数对两个表中每个名称的行进行编号，然后根据名称和行号进行联接，然后使用MERGE语句更新与ROWID伪列相关的现有表，以唯一匹配每一行:

MERGE INTO table_b dst
USING (
  SELECT a.id,
         b.rid
  FROM   (
           SELECT id,
                  name,
                  ROW_NUMBER() OVER (PARTITION BY name ORDER BY id, ROWNUM) AS rn
           FROM   table_a
         ) a
         INNER JOIN (
           SELECT ROWID as rid,
                  name,
                  ROW_NUMBER() OVER (PARTITION BY name ORDER BY id, ROWNUM) AS rn
           FROM   table_b
         ) b
         ON a.name = b.name AND a.rn = b.rn
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET id = src.id;

其中，对于样本数据:

CREATE TABLE Table_A (ID, NAME) AS
SELECT 1, 'NAME1' FROM DUAL UNION ALL
SELECT 2, 'NAME1' FROM DUAL UNION ALL
SELECT 3, 'NAME2' FROM DUAL UNION ALL
SELECT 4, 'NAME2' FROM DUAL;

CREATE TABLE Table_B (ID, NAME) AS
SELECT CAST(NULL AS NUMBER), 'NAME1' FROM DUAL UNION ALL
SELECT NULL, 'NAME1' FROM DUAL UNION ALL
SELECT NULL, 'NAME2' FROM DUAL UNION ALL
SELECT NULL, 'NAME2' FROM DUAL;

然后，在MERGE之后，表B载有: