vendredi 4 mai 2018

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?




Aucun commentaire:

Enregistrer un commentaire