CODEWITHSUNDEEP
sqlrandomplsql
mauek unak
mauek unak

Reputation: 772

Insert all values together with random values

I have 3 tables : T1, T2 and T3 and I'm trying to insert data into T1 like so :

T1.DATA1 = random values from T3.DATA1
T1.DATA2 = T2.DATA2
T1.DATA3 = T2.DATA3

I have tried this query : I have tried this query :

INSERT INTO T1 (DATA1, DATA2, DATA3)
SELECT (SELECT DATA1 FROM
(SELECT DATA1 FROM T3
ORDER BY dbms_random.value)
WHERE rownum = 1), DATA2, DATA3
FROM T2;

it returns correct values to DATA2 and DATA3, but in DATA1 I have one generated random value in all rows. How can I modify this query to have random value in every row ? T2 contains around 3000 rows.

Upvotes: 1

Views: 69

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

This subquery

SELECT DATA1 
FROM (SELECT DATA1 
      FROM T2
      ORDER BY dbms_random.value)
    WHERE rownum = 1

returns only one row, which then is repeated as many times as table T2 contains rows. That's why our query doesn't work.
To insert values in random order (if I understood what you need):

INSERT INTO T1 (DATA1, DATA2, DATA3)
select tt.data1, t.data2, t.data3
  from (select t2.data2, t2.data3, rownum rn
        from t2) t,
       (select data1, row_number() over (order by dbms_random.value) rn
          from t3,
               (select rownum from dual connect by level <= 200) m) tt
 where t.rn = tt.rn

Some explanations: subquery t returns values of data2 and data3 columns of the t2 table, subquery tt returns shuffled values of t3.data1 column (values are ordered by dbms_random.value), then both subqueries are united in one. Subquery

select rownum from dual connect by level <= 200

is used to multiply rows. If t3 table contains 15 rows, then result of the join with multiplying subquery produces 3000 rows, where each row from t3 will be repeated 200 times. To multiply by another value, put it in the query instead of 200.

Upvotes: 1

Related Questions