PostgreSQL中如何用表A的std_id随机更新表B的std_id

你原代码存在两个核心问题：

1. 逻辑错误：循环遍历的是表B的记录，但实际更新的是表A，完全搞反了操作对象；
2. 随机值复用：即使修正操作对象，原随机子查询的写法在批量场景下可能被PostgreSQL优化为只执行一次，导致所有行取同一个值，同时floor(random() * 100)假设表A固定有100条数据，灵活性极差（表A数据量变化时会出现无匹配结果的情况）。

下面给出两种可行的解决方案：

方案一：高效集合操作（推荐） #

直接用PostgreSQL的集合特性完成批量更新，无需循环，效率更高：

WITH random_a AS (
    -- 给表A的所有行随机排序并添加行号
    SELECT std_id, row_number() OVER (ORDER BY random()) AS rn
    FROM A
),
random_b AS (
    -- 给表B的所有行随机排序并添加行号，同时保留原std_id用于匹配
    SELECT std_id AS old_std_id, row_number() OVER (ORDER BY random()) AS rn
    FROM B
)
UPDATE B
SET std_id = random_a.std_id
FROM random_a
JOIN random_b ON random_a.rn = random_b.rn
WHERE B.std_id = random_b.old_std_id;

原理说明 #

• 先通过row_number() OVER (ORDER BY random())分别给表A、表B的行生成随机排序的行号；
• 按行号关联两张表，实现表B的每行对应一个随机的表A的std_id；
• 如果表A的行数多于表B，每个B的行都会拿到唯一的A的std_id；如果表A行数少于表B，会出现重复分配（若要避免重复，需额外处理，比如限制B的更新数量或重复使用A的std_id）。

方案二：修正后的PL/pgSQL循环 #

如果一定要用循环实现，需修正逻辑错误并确保每次循环重新生成随机值：

DO
$$
DECLARE
    ele record;
    random_std_id uuid; -- 假设std_id是UUID类型，若为其他类型需调整
BEGIN
    -- 遍历表B的记录，建议同时获取B的主键（比如id）来精准定位
    FOR ele IN SELECT id, std_id FROM B LOOP
        -- 每次循环随机获取一个表A的std_id
        SELECT std_id INTO random_std_id
        FROM A
        ORDER BY random()
        LIMIT 1;
        
        -- 精准更新当前B的记录（用主键id定位，避免原std_id重复导致多更）
        UPDATE B
        SET std_id = random_std_id
        WHERE id = ele.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

注意事项 #

• 必须用表B的主键（比如id）来定位更新行，不能用原std_id（如果原std_id有重复，会导致一次循环更新多条记录）；
• 循环写法的效率远低于集合操作，数据量较大时不建议使用。

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