如何循环使用另一表数据批量更新目标表空值列？

场景说明 #

• Table1包含10000行数据，value列全部为null，每行有唯一的序号列sl
• Table2存储若干组名称数据，包含序号列id_name和对应的names值
• 需求：按顺序循环使用Table2的names值填充Table1的value列——Table1第1行取Table2第1条names，第2行取第2条，到Table2末尾后从头重复，直至填满所有行

示例数据 #

初始Table1 #

sl   value
 ----------
 1    null
 2    null
 3    null
 4    null
 5    null 

Table2 #

id_name names
 1     ram 
 2     sam
 3     jam

预期更新后Table1 #

sl   value
 ----------
 1    ram
 2    sam
 3    jam
 4    ram
 5    sam

实现方案 #

核心逻辑是通过取模运算将Table1的行号映射到Table2的行号，实现循环匹配。以下是主流数据库的具体实现：

MySQL #

UPDATE table1 t1
JOIN (
    SELECT 
        sl,
        -- 计算对应Table2的行号：(sl-1)取模总条数后+1，适配从1开始的序号
        (SELECT names FROM table2 WHERE id_name = ((sl - 1) % (SELECT COUNT(*) FROM table2)) + 1) AS fill_name
    FROM table1
) t_fill ON t1.sl = t_fill.sl
SET t1.value = t_fill.fill_name;

PostgreSQL #

UPDATE table1 t1
SET value = (
    SELECT names 
    FROM table2 
    WHERE id_name = MOD(t1.sl - 1, (SELECT COUNT(*) FROM table2)) + 1
);

SQL Server #

UPDATE t1
SET value = t2.names
FROM table1 t1
JOIN table2 t2 ON t2.id_name = ((t1.sl - 1) % (SELECT COUNT(*) FROM table2)) + 1;

特殊情况处理：Table2序号不连续 #

如果Table2的id_name不是连续自增的，需要先给Table2生成临时连续序号，再进行匹配。以MySQL为例：

UPDATE table1 t1
JOIN (
    SELECT 
        sl,
        (SELECT names FROM (
            -- 给Table2生成连续的临时序号rn
            SELECT names, ROW_NUMBER() OVER (ORDER BY id_name) AS rn FROM table2
        ) t2_temp WHERE rn = ((sl - 1) % (SELECT COUNT(*) FROM table2)) + 1) AS fill_name
    FROM table1
) t_fill ON t1.sl = t_fill.sl
SET t1.value = t_fill.fill_name;

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