CODEWITHSUNDEEP
sqloracle-databaserandom
Z___
Z___

Reputation: 3

How to update a column by repositioning the values in a random order

Okay, so this table will work as an example of what I am working with. This table consists of the name of someone and the order they are in compared to others:

NAME ORDER
ZAC 1
JEFF 2
BART 3
KATE 4

My goal is to take the numbers in ORDER and reposition them randomly and update that into the table, keeping the NAME records in the same position that they were in originally.

Example of the desired result:

NAME ORDER
ZAC 3
JEFF 1
BART 4
KATE 2

Using the table above, I have tried the following solutions:

#1

Update TEST_TABLE
Set ORDER = dbms_random.value(1,4);

This resulted in the random numbers between 1 and 4 inclusive, but the numbers could repeat, so ORDER could have the same number multiple times

Example of the attempted solution:

NAME ORDER
ZAC 3
JEFF 1
BART 3
KATE 2

#2

Update TEST_TABLE
Set ORDER = (Select dbms_random.value(1,4) From dual);

This resulted in the same random number being copied into each ORDER record, so if the number came out at 3, then it would change them all to 3.

Example of the attempted solution:

NAME ORDER
ZAC 3
JEFF 3
BART 3
KATE 3

This is my first time posting to StackOverflow, and I am relatively new to Oracle, so hopefully I proposed this question properly.

Upvotes: 0

Views: 93

Answers (3)

p3consulting
p3consulting

Reputation: 4595

The simplest is to sort the data randomly and join on the "name" column:

merge into data dst 
using (
    select rownum as rn, name from (
        select name from data order by dbms_random.value()
    )
) src
on (src.name = dst.name)
when matched then
    update set ord = src.rn 
;

Upvotes: 0

D. Mika
D. Mika

Reputation: 2808

How about this?

MERGE INTO test d USING
  (SELECT rownum AS new_order,
          name
     FROM (SELECT *
             FROM test
            ORDER BY dbms_random.value)) s
   ON (d.name = s.name)
 WHEN matched THEN
   UPDATE
     SET d.sort_order = s.new_order;

The new order is build by simply sorting the original data by random values and using rownum to number those random records from 1 to N.

I use NAME to match the records, but you should use the primary key or rowid as in Littlefoot answer. Or at least an indexed column (for speed, when the table contains a lot of data), which uniquely identifies a row.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

How about this?

Sample data:

SQL> select * from test order by rowid;

NAME    C_ORDER
---- ----------
Zac           1
Jeff          2
Bart          3
Kate          4

Table is updated based on value acquired by the row_number analytic function which sorts data randomly; matches are found by the rowid value:

SQL> merge into test a
  2  using (with counter (cnt) as
  3          (select count(*) from test)
  4         select t.rowid rid,
  5                row_number() over(order by dbms_random.value(1, c.cnt)) rn
  6         from counter c cross join test t
  7        ) b
  8  on (a.rowid = b.rid)
  9  when matched then update set
 10    a.c_order = b.rn;

4 rows merged.

Result:

SQL> select * from test order by rowid;

NAME    C_ORDER
---- ----------
Zac           3
Jeff          4
Bart          1
Kate          2

SQL>

Upvotes: 2

Related Questions