Oracle SQL - Generate and update a column with dif

Question

Need help with SQL - I want to generate and update a column in every row with a different random number.

Database # Oracle 10g.

Example - When I do something like this it updates all the rows with the same number

update mytable r 
set r.generated_num = 
(select floor(dbms_random.value(100,9999999)) from dual).

Any advice?

Answer 1

If you really need a distinct random number, well, you're going to have to generate many, many more numbers than rows.

Note the following query:

with generator as
(select /*+ materialize */ rownum rn from dba_objects where rownum <= 250)
select count(distinct rv), count(rv) from
(
select trunc(dbms_random.value(1,9999999)) as rv
  from generator v1
       cross join generator v2
       cross join generator v3
 where rownum <= 10000000
)

Some example executions:

COUNT(DISTINCTRV)  COUNT(RV)
----------------- ----------
          6321429   10000000
          6321827   10000000
          6321941   10000000
          6321507   10000000

Answer 2

Looks like sub-query is the problem.

This seems to be working-

update mytable r set r.generated_num = TRUNC(dbms_random.value(1,9999999))