如何编写SQL UPDATE语句实现两表行一对一关联赋值更新

核心思路是通过窗口函数为两张表分别生成连续行号，基于行号做等值关联实现一一匹配，就能避免你之前遇到的更新问题。

适用PostgreSQL的写法 #

WITH numbered_normal AS (
    -- 为normal表按id升序生成行号，可自定义排序规则
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM normal
),
numbered_starts AS (
    -- 为starts_after表按id升序生成行号，可自定义排序规则
    SELECT id AS sa_id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM starts_after
)
UPDATE normal n
SET fk_test_id = ns.sa_id
FROM numbered_normal nn
INNER JOIN numbered_starts ns ON nn.row_num = ns.row_num
WHERE n.id = nn.id;

适用MySQL 8.0+的写法 #

UPDATE normal n
INNER JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM normal
) nn ON n.id = nn.id
INNER JOIN (
    SELECT id AS sa_id, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM starts_after
) ns ON nn.row_num = ns.row_num
SET n.fk_test_id = ns.sa_id;

注意事项 #

• 如果你需要调整匹配顺序，只需要修改OVER(ORDER BY 字段名)里的排序字段即可，不需要改动其他逻辑
• 仅两个表行号匹配的记录会被更新，若两表行数不一致，行号超出小表行数的记录不会被赋值

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