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 alias t1 for brevity.
SET (name, desc) = (SELECT ...): Sets the name and desc columns to the values returned by the subquery.
SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id: This subquery selects the name and desc from table2 where the id matches the current row in table1.
WHERE EXISTS (...): This clause ensures that the update only happens for rows in table1 that have a matching id in table2. This prevents setting name and desc to NULL if there’s no corresponding record in table2.

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 matching id is found in both tables. It sets the name and desc columns in table1 to the corresponding values from table2.

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 in table1 with a corresponding id in table2 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 in table1, 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.