Oracle数据库如何实现从字面表随机取值逐行更新目标表

原有SQL执行后所有行取到相同值的核心原因是：Oracle查询优化器会将不依赖外层查询上下文的标量子查询判定为确定性结果，只会执行一次后将结果复用给所有待更新行，不会每行触发一次子查询的随机排序逻辑。

方案1：最小改动原SQL，强制子查询依赖外层行 #

原理是给子查询增加一个和外层待更新行相关的判定条件，让Oracle识别到子查询结果和外层行相关，无法复用缓存，每行都重新执行随机取数。修改后的SQL如下：

update HISTORY h
    set (ORGANIZATION_ID, COMPANY_ID) = (
    select org_id, company_id from (
        select * from (
            select '3.11' as org_id, '11111111' as company_id from dual union
            select '3.22.3' as org_id, '22222222' as company_id from dual union 
            -- 保留你原来的其他union条目即可
            select '3.44.5' as org_id, '33333333' as company_id from dual
        ) order by DBMS_RANDOM.RANDOM
    ) where rownum = 1 
    -- 新增依赖外层行的条件，强制每行重算子查询
    and h.ROWID is not null
) where CODE = '1234567';

如果你的HISTORY表有明确的主键字段，也可以用and h.主键字段 is not null替代ROWID的判断，效果一致。

方案2：MERGE关联更新（性能更优，适合数据量大的场景） #

如果待更新行数多、字面量条目也多，方案1每行都排序取数的性能较低，可以用MERGE语句先给两类表分配随机序号再关联匹配：

MERGE INTO HISTORY h
USING (
    -- 取出所有待更新的行，分配随机序号
    SELECT 
        ROWID as h_rowid,
        ROW_NUMBER() OVER(ORDER BY DBMS_RANDOM.RANDOM) as rn
    FROM HISTORY
    WHERE CODE = '1234567'
) h_rand
ON (h.ROWID = h_rand.h_rowid)
WHEN MATCHED THEN UPDATE SET
    ORGANIZATION_ID = (select org_id from (
        select org_id, ROW_NUMBER() OVER(ORDER BY 1) as org_rn, count(*) over() as total from (
            select '3.11' as org_id, '11111111' as company_id from dual union
            select '3.22.3' as org_id, '22222222' as company_id from dual union
            select '3.44.5' as org_id, '33333333' as company_id from dual
        )
    ) where org_rn = mod(h_rand.rn -1, total) + 1),
    COMPANY_ID = (select company_id from (
        select company_id, ROW_NUMBER() OVER(ORDER BY 1) as org_rn, count(*) over() as total from (
            select '3.11' as org_id, '11111111' as company_id from dual union
            select '3.22.3' as org_id, '22222222' as company_id from dual union
            select '3.44.5' as org_id, '33333333' as company_id from dual
        )
    ) where org_rn = mod(h_rand.rn -1, total) + 1);

更新完成后可以执行以下语句验证取值分布是否符合预期：

SELECT ORGANIZATION_ID, COMPANY_ID, count(*) as cnt
FROM HISTORY
WHERE CODE = '1234567'
GROUP BY ORGANIZATION_ID, COMPANY_ID;

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