UPDATE语句中子查询无法实时更新，如何分配唯一随机address_id？

核心思路 #

要实现给organization表的address_id分配随机且唯一的address表ID，关键是先将两个表的行分别随机排序，再按顺序一一对应关联，避免原语句中子查询仅执行一次导致所有行ID相同的问题。

PostgreSQL 实现方案 #

利用窗口函数ROW_NUMBER()为两个表的随机排序结果分配行号，通过行号关联完成更新：

WITH org_ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM organization
),
addr_ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM address
)
UPDATE organization o
SET address_id = a.id
FROM org_ranked ork
JOIN addr_ranked ark ON ork.rn = ark.rn
WHERE o.id = ork.id;

MySQL 8.0+ 实现方案 #

同样使用窗口函数ROW_NUMBER()，语法适配MySQL特性：

UPDATE organization o
JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
    FROM organization
) org_ranked ON o.id = org_ranked.id
JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
    FROM address
) addr_ranked ON org_ranked.rn = addr_ranked.rn
SET o.address_id = addr_ranked.id;

MySQL 5.x 兼容方案（无窗口函数） #

通过自定义变量生成行号，实现随机匹配：

SET @org_rn := 0;
SET @addr_rn := 0;

UPDATE organization o
JOIN (
    SELECT id, @org_rn := @org_rn + 1 AS rn
    FROM organization
    ORDER BY RAND()
) org_ranked ON o.id = org_ranked.id
JOIN (
    SELECT id, @addr_rn := @addr_rn + 1 AS rn
    FROM address
    ORDER BY RAND()
) addr_ranked ON org_ranked.rn = addr_ranked.rn
SET o.address_id = addr_ranked.id;

注意事项 #

• 如果address表的行数少于organization表，上述语句会让部分organization行的address_id仍为NULL；若要覆盖所有行需允许重复分配，但这违背唯一要求，建议确保address表行数≥organization表行数。

内容的提问来源于stack exchange，提问作者dev-rifaii