How Can I Update an Oracle Table's Data Using Values from Another Table?

Oracle SQL: Updating Table Data Using Values from Another Table

Frequently, database management requires updating one table's data with values sourced from another. Let's illustrate this with two sample tables:

Table 1:

<code>id    name    desc
-----------------------
1     a       abc
2     b       def
3     c       adf</code>

Table 2:

<code>id    name    desc
-----------------------
1     x       123
2     y       345</code>

Our goal is to update Table 1's name and desc columns with data from Table 2, matching on the id column. The desired outcome:

Table 1 (Updated):

<code>id    name    desc
-----------------------
1     x       123
2     y       345
3     c       adf</code>

Oracle SQL offers efficient methods for this type of correlated update:

Method 1: Correlated Subquery

This approach uses a correlated subquery within the UPDATE statement:

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 );

This query updates each row in Table 1 where a matching id exists in Table 2.

Method 2: Using a Common Table Expression (CTE)

Alternatively, if the join creates a key-preserved view, a CTE provides a cleaner solution:

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2;

This method constructs a temporary view (using a CTE) combining relevant fields from both tables, then performs the update on this view.

Both methods effectively update Table 1 based on data in Table 2, demonstrating flexible techniques for data manipulation in Oracle SQL.

The above is the detailed content of How Can I Update an Oracle Table's Data Using Values from Another Table?. For more information, please follow other related articles on the PHP Chinese website!