CODEWITHSUNDEEP
sqloracle-database
Niraj Bahl
Niraj Bahl

Reputation: 1

Update Column Using Random Unique Values from a Different Table

I have a table say Table1 with Columns Referral_ID and Company Number, I have another table Table2 with a lot of unique Address IDS and Company Number.

I need to bring a Unique Address ID randomly from table2 to table1 where company code = company code?

Can i do this with Oracle SQL?

Upvotes: 0

Views: 193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Hmmmm . . . You can use a lateral join if you want an arbitrary address rather than a random address:

select t1.*, t2.*
from table1 t1 left join lateral
     (select t2.*
      from table2 t2
      where t2.company_number = t1.company_number and rownum = 1
     ) t2
     on 1=1;

If you actually want a random address that is the same per company number, you can use:

select t1.*, t2.*
from table1 t1 left join lateral
     (select t2.*,
             row_number() over (partition by company_number order by dbms_random.random) as seqnum
      from table2 t2
     ) t2
     on t2.company_number = t1.company_number and
        seqnum = 1;

Here is a db<>fiddle illustrating that the syntax works.

Upvotes: 1

Related Questions