I want to randomly pick a data tuple from a literal table to fill in another table, and randomize the row to pick each time. With the below query, I fill all rows with the same data, so I think the random row is picked once and used everywhere.

So how can I get a random row for each row to update in the Oracle database?

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

CodePudding user response：

As per my similar answer to another question on picking random rows, you can do it but you need to add some seemingly irrelevant filters to force the SQL optimiser to not materialize the sub-query and to ensure the values are randomly generated for each row:

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;

Which, for the sample data:

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;

Then after the MERGE, the table may (randomly) contain:

ORGANIZATION_ID	COMPANY_ID	CODE
3.44.5	33333333	1234567
3.11	11111111	1234567
3.44.5	33333333	1234567
3.22.3	22222222	1234567
3.11	11111111	1234567

db<>fiddle here