Reputation: 2264
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
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);
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