Oracle database - update table with random value from literal table, but change data per row

Question

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

MT0 · Accepted Answer · 2021-09-30 13:22:36Z

2

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

edited Sep 30, 2021 at 13:22

answered Sep 29, 2021 at 20:28

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

WesternGun Over a year ago

Thanks! I know it's complicated, but didn't expect this much.

WesternGun Over a year ago

Hi, one thing I don't understand: where is the constraint of "where CODE = '1234567'"?

WesternGun Over a year ago

I think it should be added after h.ROWID = d.RID, like (h.ROWID = d.RID and h.CODE = '1234567'). OK now it works.

MT0 Over a year ago

@WesternGun Yes, that would work or you can add a WHERE clause to the end of the SELECT query in the USING clause (which may be slightly more efficient as you then would not need to compare all the rows in the ON clause).

WesternGun Over a year ago

One thing to note is that, in sqlcl of Oracle it is fine, but Intellij IDEA plugin Database Navigator is not recognizing the query as legit. Will fire a bug to them.

|

Collectives™ on Stack Overflow

Oracle database - update table with random value from literal table, but change data per row

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

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

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related