vendredi 26 août 2016

Selecting a Random Row in Oracle

I need to randomly select values from one table, e.g. tableA.a_id which is a VARCHAR2, and use the value to insert into another table. For instance, assuming three columns needs to be inserted into 100 rows of tableX (a sequence number, a random number between 100 and 999, and values of tableA.a_id):

insert into tableX
select
    rownum,
    dbms_random.value(100,999), 0),
    (select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
    where rownum = 1)
from
   (select level from dual connect by level <= 100);

However, rather than picking a random row from tableA.a_id for each row, it selects the same value for all the rows, e.g.:

1 129 A-ID-48
2 849 A-ID-48
3 367 A-ID-48

However, if I execute the subquery repeatedly, I get a new value each time (for obvious reason), e.g.:

select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
where rownum = 1;

Result would be after each execution:

A-ID-7
A-ID-48
A-ID-74

How do I alter the original query, or come up with a new one for that matter, that would insertion of random rows from tableA's a_id column for each insert row into the destination table? Desire outcome:

1 129 A-ID-7
2 849 A-ID-48
3 367 A-ID-74




Aucun commentaire:

Enregistrer un commentaire