How to Update Oracle Table Data from Another Table Using SQL?

Using SQL to update Oracle table data from another table

In relational database management, it is often necessary to update data in one table using values ​​from another table. This can be accomplished using the SQL UPDATE statement.

Scene:

Consider two tables, Table 1 and Table 2, which contain the following data:

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

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

Target:

Update Table 1 with the name and desc columns from Table 2 when the id columns in both tables match. The expected results are as follows:

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

Option 1: Related updates

In Oracle SQL, related updates are available:

<code class="language-sql">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 )</code>

Instructions:

This query performs an update on table1, using a subquery to retrieve the corresponding name and desc values ​​from table2 based on the matching id. The WHERE EXISTS clause ensures that only rows in table1 that have matching rows in table2 are updated.

Option 2: JOIN-based update

Assuming that the result produced by the join preserves the keys, the following update statement can be used:

<code class="language-sql">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</code>

Instructions:

This query performs an update on the join view, effectively updating the name and desc columns of table1 to the values ​​matched by the corresponding id in table2.

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