sql - Oracle update based on rownum -
i need write oracle sql update column value unrelated table using rownum.
i cannot work:
update table_1 set a.id = (select b.id table_2 b a.rownum = b.rownum)
thanks.
just need insert value column id table. there no columns can use join rownum. possible?
use merge statement instead of update.
please find simple example below.
test data first (id
column in table_2
null):
create table table_2 select level id, chr(64+level) name dual connect level <= 5; create table table_1 select * table_2; update table_2 set id = null; commit; select * table_1; id name ---------- ---- 1 2 b 3 c 4 d 5 e select * table_2; id name ---------- ---- b c d e
this merge command copier id
values 1 table second 1 basing on rownumns:
merge table_2 t2 using ( select * ( select t.*, rownum rn table_1 t ) t1 join ( select rownum rn, rowid rid table_2 t ) t2 on t1.rn = t2.rn ) d on ( t2.rowid = d.rid ) when matched update set t2.id = d.id;
and result after merge is:
select * table_2; id name ---------- ---- 1 2 b 3 c 4 d 5 e
Comments
Post a Comment