Updating Tables with Data from Other Tables in Oracle SQL
In relational databases, a common task is to update data in one table based on corresponding data in another table. This is frequently needed for data synchronization, correction, or enrichment. This tutorial focuses on how to achieve this in Oracle SQL, covering several approaches with explanations and examples.
The Scenario
Imagine you have two tables: table1
and table2
. Both tables share a common column, id
, which serves as a key to link related records. The goal is to update the name
and desc
columns in table1
with the corresponding values from table2
based on matching id
values.
Here’s an example of what the tables might look like initially:
Table 1:
id name desc
-----------------------
1 a abc
2 b def
3 c adf
Table 2:
id name desc
-----------------------
1 x 123
2 y 345
After the update operation, table1
should look like this:
id name desc
-----------------------
1 x 123
2 y 345
3 c adf
Methods for Updating Tables
Here are several ways to achieve this update in Oracle SQL:
1. Using a Subquery in the UPDATE
Statement
This is a straightforward approach where a subquery retrieves the new values from table2
based on the matching id
in table1
.
UPDATE table1 t1
SET (name, desc) = (SELECT t2.name, t2.desc
FROM table2 t2
WHERE t1.id = t2.id)
WHERE EXISTS (
SELECT 1
FROM table2 t2
WHERE t1.id = t2.id );
Explanation:
UPDATE table1 t1
: Specifies the table to be updated and assigns an aliast1
for brevity.SET (name, desc) = (SELECT ...)
: Sets thename
anddesc
columns to the values returned by the subquery.SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id
: This subquery selects thename
anddesc
fromtable2
where theid
matches the current row intable1
.WHERE EXISTS (...)
: This clause ensures that the update only happens for rows intable1
that have a matchingid
intable2
. This prevents settingname
anddesc
toNULL
if there’s no corresponding record intable2
.
2. Using MERGE
Statement
The MERGE
statement is a powerful and often efficient way to perform conditional updates, inserts, and deletes in a single statement.
MERGE INTO table1 t1
USING table2 t2
ON (t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;
Explanation:
MERGE INTO table1 t1
: Specifies the target table for the merge operation.USING table2 t2
: Specifies the source table.ON (t1.id = t2.id)
: Defines the join condition between the two tables.WHEN MATCHED THEN UPDATE SET ...
: This clause executes the update when a matchingid
is found in both tables. It sets thename
anddesc
columns intable1
to the corresponding values fromtable2
.
3. Using a Join in a Subquery (Alternative UPDATE
Approach)
This method utilizes an implicit join within the UPDATE
statement.
UPDATE table1 t1
SET (name, desc) = (SELECT t2.name, t2.desc
FROM table2 t2
WHERE t1.id = t2.id)
WHERE t1.id IN (SELECT t2.id FROM table2 t2);
Explanation:
- The
WHERE t1.id IN (SELECT t2.id FROM table2 t2)
clause ensures that only rows intable1
with a correspondingid
intable2
are updated.
Important Considerations:
- Key Preservation: Ensure that the join condition uniquely identifies the rows to be updated. If multiple rows in
table2
match a single row intable1
, the subquery might return more than one row, causing an error. - Performance: The
MERGE
statement is often the most efficient method, especially for larger tables. However, the performance can vary depending on the database and indexes. Consider testing different approaches to determine the best solution for your specific needs. - Transaction Management: Always execute
UPDATE
statements within a transaction to ensure data consistency. This allows you to roll back the changes if an error occurs. - Backup: Before performing any significant data modification, it’s always a good practice to back up your database.