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