Update data for another table with data from one table in Oracle

update data for another table with data from one table in OracleCategory: Sql/plsql2012-05-04 15:49 4153 People read Comments (1) favorite reports Oraclemergesubqueryinsertnull

There are two tables that update Val in the table TAB1 with the ID value in the same row as the ID value in table TaB2 to the Val value in TaB2.
SELECT * from Tab1;

SELECT * FROM TAB2

The easiest error to make is: Update tab1 set val= (select Val from tab2 where tab1.id=tab2.id);
The result of the update is: SELECT * from Tab1, in the Tab1 row, if there is no corresponding row in the TAB2, the value is updated to null

Corrected to: Update TAB1 set val= (select Val from tab2 where Tab1.id=tab2.id)
where exists (select 1 from tab2 where tab1.id=tab2.id)
However, if there are multiple tab2 in the TAB1, there will be an error.
The best way is to use the merge syntax:
[SQL]View Plaincopy

1. Merge into tab1
2. Using TAB2
3. On (tab1.id=tab2.id)
4. When matched then
5. Update Set tab1.val = Tab2.val

Similarly, if there are multiple cases in tab2 that correspond to one of the TAB1, an error will also occur: Ora-30926:unable to get a stable set of rows in the source tables
Like inserting an INSERT into TAB2 values (2, ' xxxx ') in TAB2
This error can be avoided by repeating record filtering in the subquery in the using, Merge Ultimate version:
[SQL]View Plaincopy

1. Merge into tab1
2. using (select * from tab2 X WHERE x.rowid =
3. (SELECT MAX (y.rowid) from tab2 Y WHERE x.id = y.id)) tab2
4. On (tab1.id=tab2.id)
5. When matched then
6. Update Set tab1.val = Tab2.val

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Update data for another table with data from one table in Oracle