时间戳

首页 > 解决方案 > Oracle 数据库 - 使用文字表中的随机值更新表，但每行更改数据

sql - Oracle 数据库 - 使用文字表中的随机值更新表，但每行更改数据

问题描述

我想从文字表中随机选择一个数据元组来填充另一个表，并随机化每次选择的行。使用下面的查询，我用相同的数据填充所有行，所以我认为随机行被挑选一次并在任何地方使用。

那么如何在 Oracle 数据库中为每一行获取一个随机行来更新呢？

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 
            ...
            select '3.44.5' as org_id, '33333333' as company_id from dual
        ) order by DBMS_RANDOM.RANDOM
    ) where rownum = 1
) where CODE = '1234567'; -- originally all were 3.88.4 and 88000004. 20707 rows

标签： sqloracle

解决方案

根据我对关于选择随机行的另一个问题的类似回答，您可以这样做，但您需要添加一些看似不相关的过滤器以强制 SQL 优化器不实现子查询并确保为每一行随机生成值：

MERGE INTO HISTORY h
USING (
  WITH data (org_id, company_id) AS (
    select '3.11',   '11111111' from dual union
    select '3.22.3', '22222222' from dual union 
    select '3.44.5', '33333333' from dual
  )
  SELECT h.ROWID AS rid,
         d.*
  FROM   history h
         CROSS JOIN LATERAL (
           SELECT *
           FROM   data
           WHERE  ROWNUM > 0          -- force a new random on each row
           AND    h.ROWID IS NOT NULL -- force the query to correlate
           ORDER BY DBMS_RANDOM.VALUE() DESC
           FETCH FIRST ROW ONLY
         ) d
  WHERE  h.code = '1234567'
) d
ON ( h.ROWID = d.RID )
WHEN MATCHED THEN
  UPDATE
  SET ORGANIZATION_ID = d.org_id,
      COMPANY_ID      = d.company_id;

其中，对于样本数据：

CREATE TABLE history (organization_id, company_id, code) AS
SELECT '123456778', '88888888888', '1234567' FROM DUAL UNION ALL
SELECT '123456778', '88888888888', '1234567' FROM DUAL UNION ALL
SELECT '123456778', '88888888888', '1234567' FROM DUAL UNION ALL
SELECT '123456778', '88888888888', '1234567' FROM DUAL UNION ALL
SELECT '123456778', '88888888888', '1234567' FROM DUAL;

然后在 之后MERGE，表格可能（随机）包含：

ORGANIZATION_ID COMPANY_ID 代码
3.44.5 33333333 1234567
3.11 11111111 1234567
3.44.5 33333333 1234567
3.22.3 22222222 1234567
3.11 11111111 1234567

db<>在这里摆弄

推荐阅读