CODEWITHSUNDEEP
sqloracle-databaserandom
Lee Greco
Lee Greco

Reputation: 743

Oracle update using random map

I have a need to update records in a table changing the order of the rows randomly similar to shuffling a deck of cards. The records have Id, Name, and Ordinal fields where Ordinal is initially set to the same value as Id. I need to randomize the order of a range of records and not all records in the table. Ordinal needs to be unique at the end of the update.

Initial records:

Id  Name      Ordinal
--- --------- -------
  1 Item 1          1
  2 Item 2          2
  3 Item 3          3
  4 Item 4          4
  5 Item 5          5
  6 Item 6          6

What I want after update:

Id  Name      Ordinal
--- --------- -------
  1 Item 1          5
  2 Item 2          2
  3 Item 3          4
  4 Item 4          1
  5 Item 5          6
  6 Item 6          3

I've tried the following (simplified to use only the first 50 records). The core of the inner select does return a good map of values, but I'm seeing duplicate and skipped values in the results (notice 2 5's and no 2's):

UPDATE cards 
SET Ordinal = (SELECT ordinal_to 
  FROM (SELECT ROWNUM as ordinal_from, r as ordinal_to 
    FROM (SELECT ROWNUM r, DBMS_RANDOM.VALUE as rv 
      FROM DUAL CONNECT BY ROWNUM <= 50 ORDER BY rv)
  ) WHERE ordinal_from = cards.Ordinal
) WHERE cards.Id BETWEEN 1 and 50;

What I'm getting after update:

Id  Name      Ordinal
--- --------- -------
  1 Item 1          5
  2 Item 2          3
  3 Item 3          4
  4 Item 4          1
  5 Item 5          6
  6 Item 6          5

Apparently the ordinal_from/ordinal_to values are being calculated and randomized for each updated record instead of once at the beginning of the update.

I know I could create a temporary table to hold the mapping values and use it for the update, but I'd rather avoid a temporary table.

Is there any way to do this in a single SQL command that doesn't use a temporary table?

Upvotes: 0

Views: 62

Answers (1)

user5683823
user5683823

Reputation:

Here is one way to do this. You don't need a temporary table. The merge statement is often better than a straight update when you update values in one table based on values (stored or calculated) from another table - even if it is the same table.

Full example (including the creation of the table):

create table cards as
  select 1 as id, 'Item 1' as name, 1 as ordinal from dual union all
  select 2      , 'Item 2'        , 2 as ordinal from dual union all
  select 3      , 'Item 3'        , 3 as ordinal from dual union all
  select 4      , 'Item 4'        , 4 as ordinal from dual union all
  select 5      , 'Item 5'        , 5 as ordinal from dual union all
  select 6      , 'Item 6'        , 6 as ordinal from dual
;

merge into cards s
  using ( select rowid as rid,
                 row_number() over (order by dbms_random.value()) as rn
          from   cards
        ) t
    on (s.rowid = t.rid)
when matched then update set s.ordinal = t.rn
;

select * from cards;

        ID NAME      ORDINAL
---------- ------ ----------
         1 Item 1          3
         2 Item 2          6
         3 Item 3          4
         4 Item 4          5
         5 Item 5          2
         6 Item 6          1

Upvotes: 1

Related Questions