How Can I Update an Oracle Table Using Correlated Subqueries?

Using correlated subqueries to update table data in Oracle SQL

When table data needs to be updated from another table based on matching IDs, Oracle SQL provides a powerful mechanism called related updates. This approach enables seamless updates by using subqueries in SQL statements.

Let’s look at an example where we will update Table 1 with data from Table 2 based on its corresponding ID.

<code>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</code>

To accomplish this update in Oracle SQL, we can use the following query:

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 uses a correlated subquery to extract matching data from Table 2 based on the ID in Table 1. The WHERE clause ensures that only rows in Table 1 that have corresponding rows in Table 2 are updated.

Alternatively, if the join result is a key-preserving view, you can also use the following query:

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

This query performs an update in a derived table, where matching data from both tables are joined by the ID column. The subsequent SET clause updates the columns in Table 1 with the values ​​from Table 2.

By using these related update technologies, we can efficiently update data in Oracle SQL, ensuring seamless synchronization and data consistency between tables.

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