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?
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))
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
