CODEWITHSUNDEEP
sqlteradata
cloud36
cloud36

Reputation: 1066

Randomly Assign Records From One Table to Another

I have two tables.

T1
id,date,item,channel

T2
id,date,item,channel

In T2 the id and date columns are NULL. I want to randomly assign an id and date from T1 to each row in T2. Also, T2 is much smaller than T1.

Any ideas how to do this? I'm on Teradata 13.

I was originally thinking something like this:

sel count(*) from t2 ;
--507

select *
from (sel a.*, RANDOM(1,507) as r1 from t1) a
inner join (sel b.*, RANDOM(1,507) as r1 from t2) b
on a.r1 = b.r1

The problem is I'll need to automatically assign the count of t2 as the upper bound of random.

Upvotes: 1

Views: 434

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Assign a ROW_NUMBER based on a RANDOM sort and join on that:

select *
from
 (
   select dt.*, row_number() over (order by t1.r) as rn
   from
    ( 
      select t1.*, RANDOM(1,1000000) as r from t1
    ) as dt
 ) as a
join
 (
   select dt.*, row_number() over (order by t1.r) as rn
   from
    ( 
      select t2.*, RANDOM(1,1000000) as r from t2
    ) as dt
 ) as b   
  on a.rn = b.rn

Upvotes: 1

Related Questions