oracle - PL/SQL - Update multiple rows in the target table from one row in the source table -
this question has answer here:
i'm using merge statement merge 2 tables 1 row in source table may update multiple rows in target table.
it goes bit this
merge table1 using (select emp_code, days_off table2) b on (a.id = b.emp_code) when matched update set a.days_off = b.days_off; however, when attempt this, sql error: ora-30926: unable stable set of rows in source tables
is there other way can this?
i sql error: ora-30926: unable stable set of rows in source tables
because, source table contains duplicate values.
you need add 1 more column uniquely identify each row.
create table source_table ( col1 number, col2 varchar2(10), col3 varchar2(10) ); insert source_table (col1, col2, col3) values (1, 'a', 'w'); insert source_table (col1, col2, col3) values (1, 'b', 'x'); insert source_table (col1, col2, col3) values (2, 'c', 'y'); insert source_table (col1, col2, col3) values (3, 'c', 'z'); commit; create table target_table ( col1 number, col2 varchar2(10), col3 varchar2(10) ); insert target_table (col1, col2, col3) values (1, 'b', 'z'); insert target_table (col1, col2, col3) values (3, 'd', 'w'); commit; now going merge 2 table.
merge target_table trg using (--actually can write source_table example want write select:) select col1, col2, col3 source_table ) src on (trg.col1 = src.col1) when matched update set --don't forget cannot update columns included in on clause trg.col2 = src.col2, trg.col3 = src.col3 when not matched insert ( col1, col2, col3 ) values ( src.col1, src.col2, src.col3 ); commit; solution
merge target_table trg using source_table src --now write table name:) on ( trg.col1 = src.col1 , trg.col2 = src.col2 ) when matched update set --don't forget cannot update columns included in on clause trg.col3 = src.col3 when not matched insert ( col1, col2, col3 ) values ( src.col1, src.col2, src.col3 ); commit;
Comments
Post a Comment