Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.
Suppose we have two tables Categories and Categories_Test. See screenshots below.
lets take two tables TABA & TABB:
Records in TABA:
Records in TABB:
1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.
The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.
update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);
2. Update data in two columns in table A based on a common column in table B.
If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.
update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);
