CODEWITHSUNDEEP
sqloracle10g
Thiyagu ATR
Thiyagu ATR

Reputation: 2264

How to update entire column into another table using oracle?


I have two tables called t1(id,ref_id) and t2(ref_id,id) I need to update t1 's ref_id column which is empty using data from t2 ref_id .here id t1.id and t2.id has same value let's consider.
t1 has 

ID  REF_ID
123  
123  
124  
124  
124  
125

t2 has

REF_ID  ID
2010    123
2011    123
2012    124
2013    125
2014    124
2015    124

and my output would be

123 2010
123 2011
124 2012
125 2013
124 2014
124 2015

can that be done by a single update statement? i have tried this one

update test_01 t1 set t1.ref_id=(select * from(select t2.ref_id from test_02 t2 order by t2.id ));

but no result.

Upvotes: 2

Views: 1536

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

Try this:

update t1
set ref_id = (select t22.ref_id
                from (select rowid ri,  id, ref_id, row_number() over (partition by id order by 1) rn 
                        from t1 ) t11 join
                     (select id, ref_id, row_number() over (partition by id order by 1) rn
                        from t2) t22 on t11.id = t22.id and t11.rn = t22.rn
             where t11.ri = t1.rowid);

Here is a sqlfiddle demo

Since I used a window function (row_number) you might consider doing it with a merge:

merge into t1 d
using (
  select t22.ref_id, t11.ri
                from (select rowid ri,  id, ref_id, row_number() over (partition by id order by 1) rn 
                        from t1 ) t11 join
                     (select id, ref_id, row_number() over (partition by id order by 1) rn
                        from t2) t22 on t11.id = t22.id and t11.rn = t22.rn  
) s
on (d.rowid = s.ri)
when matched then update set d.ref_id = s.ref_id

Here is another sqlfiddle demo

Upvotes: 2

Related Questions