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