如何基于另一表的值排序，用row_number()更新表空列？

问题描述 #

test_tab表存在空列id，需要基于employee表的birthdate字段降序排序，用row_number()生成的序号填充该空列。已编写的CTE可以正常查询出结果，但后续的更新语句无法生效。

可正常运行的CTE代码 #

WITH testy(a, b, c) AS (
    SELECT 
        t1.empno, 
        t2.birthdate, 
        ROW_NUMBER() OVER(ORDER BY t2.birthdate DESC) AS order_by_id  
    FROM test_tab AS t1 
    JOIN employee AS t2 ON t2.empno = t1.empno
)

失效的更新语句 #

WITH testy(a, b, c) AS (
    SELECT 
        t1.empno,
        t2.birthdate,
        ROW_NUMBER() OVER(ORDER BY t2.birthdate DESC) AS order_by_id  
    FROM test_tab AS t1 
    JOIN employee AS t2 ON t2.empno = t1.empno
)
UPDATE test_tab
SET test_tab.id = testy.b
WHERE test_tab.empno = testy.a

失效原因分析 #

1. 关联逻辑错误：多数数据库中，UPDATE语句直接在WHERE子句引用CTE列无法建立有效关联，需要显式将目标表与CTE通过JOIN绑定。
2. 赋值列错误：需求是用row_number()生成的序号填充id，但语句中错误地将birthdate（testy.b）赋值给id，应该使用CTE中的order_by_id（testy.c）。

正确的更新语句 #

适用于SQL Server、PostgreSQL的写法 #

WITH testy(a, c) AS (
    SELECT 
        t1.empno,
        ROW_NUMBER() OVER(ORDER BY t2.birthdate DESC) AS order_by_id  
    FROM test_tab AS t1 
    JOIN employee AS t2 ON t2.empno = t1.empno
)
UPDATE t
SET t.id = testy.c
FROM test_tab t
INNER JOIN testy ON t.empno = testy.a;

适用于MySQL 8.0+的写法 #

WITH testy(a, c) AS (
    SELECT 
        t1.empno,
        ROW_NUMBER() OVER(ORDER BY t2.birthdate DESC) AS order_by_id  
    FROM test_tab AS t1 
    JOIN employee AS t2 ON t2.empno = t1.empno
)
UPDATE test_tab t
JOIN testy ON t.empno = testy.a
SET t.id = testy.c;

补充说明 #

• 如果需要保留CTE中的birthdate字段，可在CTE中保留，但赋值时务必使用order_by_id列。
• 确保empno是两张表的有效关联键，且无重复值，避免row_number()生成的序号出现逻辑错误。

内容的提问来源于stack exchange，提问作者user20503658