How Can I Update an Oracle Table with Data from Another Using SQL?

Using SQL to Update an Oracle Table with Data from Another

Oracle SQL offers efficient methods for updating one table with data from another. This can be achieved using correlated updates or alternative syntax.

Let's illustrate with example tables:

<code class="language-sql">Table 1:
id | name | description
-----------------------
1   | a     | abc
2   | b     | def
3   | c     | adf</code>

<code class="language-sql">Table 2:
id | name | description
-----------------------
1   | x     | 123
2   | y     | 345</code>

Method 1: Correlated Subquery Update

This approach uses a correlated subquery to fetch matching data from Table 2 for each row in Table 1:

<code class="language-sql">UPDATE table1 t1
SET (name, description) = (SELECT t2.name, t2.description
                         FROM table2 t2
                        WHERE t1.id = t2.id)
WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id );</code>

The WHERE EXISTS clause ensures that only rows with matching IDs in both tables are updated.

Method 2: Alternative Syntax using a Subquery

This method employs a subquery to create a temporary view combining both tables, then updates this view:

<code class="language-sql">UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.description desc1,
               t2.name name2,
               t2.description desc2
          FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2;</code>

This approach leverages a JOIN to efficiently combine the tables. The update then modifies the selected columns (name1, desc1) based on the corresponding columns from Table 2 (name2, desc2). The id column serves as the join key, ensuring accurate updates to Table 1.

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