There are two tables: update the Val value of the row with the same ID value in Table tab1 and that in Table tab2 to the Val value in table 2.
Select * From tab1;
Select * From tab2
The most common mistake is: Update tab1 set val = (select Val from tab2 where tab1.id = tab2.id );
The updated result is: Select * From tab1, which has rows in tab1. If no corresponding row exists in 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 multiple entries in tab2 correspond to one entry in tab1, an error occurs.
The best way is to use merge Syntax:
Merge into tab1using tab2on (tab1.id = tab2.id) when matched thenupdate set tab1.val = tab2.val
Similarly, an error occurs if multiple entries in tab2 correspond to one entry in tab1: ORA-30926: unable to get a stable set of rows in the source tables
For example, insert an insert into tab2 values (2, 'xxxx') in tab2 ')
This error can be avoided by filtering repeated records in the subquery of using. For merge future edition:
merge into tab1using (select * From tab2 X where X. rowid = (select max (Y. rowid) from tab2 y Where X. id = y. ID) tab2on (tab1.id = tab2.id) when matched thenupdate set tab1.val = tab2.val