Rakesh Nair Rakesh Nair - 3 months ago 10
SQL Question

Update a column by selecting random value from a different column

I have a table with the following structure

+----+---+
| A | B |
+----+---+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
+----+---+


I need to update the column
B
by selecting any of the element randomly from column
A
.

I used the query to get a random value from column
A
.

SELECT A FROM
( SELECT A FROM MyTable
ORDER BY dbms_random.value )
WHERE rownum = 1


Then I tried this query to update the value
B


UPDATE MyTable SET B=( SELECT A FROM
( SELECT A FROM MyTable
ORDER BY dbms_random.value )
WHERE rownum = 1);


But this will set all the rows with same value. But I need each row to be set with random values.

How can I achieve this in
Oracle 11g
?

Answer

This is the query you are trying:

UPDATE MyTable
    SET B = (SELECT A
             FROM (SELECT A FROM MyTable ORDER BY dbms_random.value)
             WHERE rownum = 1
            );

I think the problem is that the optimizer is too smart -- it sees the subquery and executes it just once. In other databases, this can be fixed by using a correlation clause. But, Oracle does not allow that in subqueries nested more than one level.

So, here is a slightly different formulation:

UPDATE MyTable t
    SET B = (SELECT MAX(A) KEEP (DENSE_RANK FIRST ORDER BY dbms_random.value)
             FROM MyTable t2
             WHERE t2.A <> t.A  -- This is an arbitrary correlation clause to ensure that the subquery runs for each row
            );
Comments