I need to insert data into a table which is already having some data into it now a new column is added to the table and we need to insert/update the new column with values from another table.
create table target_tab (fname varchar2(20), acc_no number);
insert into target_tab values('Anybody',121);
insert into target_tab values('Somebody',122);
insert into target_tab values('Nobody',123);
alter table target_tab add sc_vlan varchar2(20);
create table source_tab (rsrc_nm varchar2(20));
insert into source_tab values ('2839_124');
insert into source_tab values('2839_125');
insert into source_tab values('2839_126');
insert into source_tab values('2840_131');
insert into source_tab values('2841_132');
insert into source_tab values('2840_134');
insert into source_tab values('2840_127');
Now we need to insert the values of rsrc_nm column from source_tab to the scvlan column of the target_tab.
Note : we do not have any relationship between both tables and like we see in example that target_tab has less rows than source_tab then only row which are in target_tab should be insert/update with unique values from source_tab. We can fetch any value from source_tab.
declare
cursor c_1 is select rsrc_nm
from source_tab
where rownum <= (select count(1)
from target_tab);
type t_tab is table of c_1%rowtype index by pls_integer;
l_tab t_tab;
begin
open c_1;
fetch c_1 bulk collect into l_tab;
for i in 1..l_tab.count
loop
update target_tab set sc_vlan = l_tab(i).rsrc_nm
where sc_vlan is null
and rownum = 1;
commit;
exit when c_1%notfound;
end loop;
close c_1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace());
end;
I think You could use merge like this:
merge into target_tab t
using (
select s.rsrc_nm, t.rwd
from (select row_number() over (order by acc_no) rn, t.*, rowid rwd
from target_tab t) t
join (select row_number() over (order by rsrc_nm) rn, s.*
from source_tab s) s
on t.rn =s.rn
) s
on (t.rowid = s.rwd)
when matched then update set t.sc_vlan = s.rsrc_nm
Sort data in both tables on any columns, match them using row_number and update.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments