How to Update an Oracle SQL Table with Data from Another Table?

Update table data using Oracle SQL

When dealing with relational databases such as Oracle SQL, you may encounter scenarios where you need to update a table with data from another table. This task is typically performed using SQL's UPDATE command.

Question:

Suppose there are two tables, Table 1 and Table 2, both containing id, name and desc columns, and you are required to update the data in Table 1 with the corresponding data in Table 2 based on the matching id values. The desired result is that table 1 should be updated with the name and desc values ​​corresponding to the id values ​​in table 2.

Solution:

To implement this update, you can use the relevant update in Oracle SQL. The following is the detailed method:

<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:

• The UPDATE command starts the update operation on table 1, aliasing it as t1.
• The SET clause specifies that the name and desc columns of Table 1 will be updated with the values ​​from Table 2.
• The subquery in the SET clause retrieves the corresponding name and desc values ​​for each row in Table 1 from Table 2 (t2).
• The WHERE clause in the subquery ensures that only rows with matching id values ​​are updated.
• The outer WHERE clause further checks whether there is a row in Table 2 that matches each row in Table 1. This ensures that only rows with valid matches are updated.

Alternative approach (assuming the key retains the view):

If the join between Table 1 and Table 2 results in a key-preserving view, you can also use the following method:

<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 method assumes that the key-preserving view has been created as an intermediate step.
• The first part is a subquery that performs a join between Table 1 and Table 2.
• Keeping the view based on the key, you can directly update the name and desc columns of table 1 with the values ​​in the intermediate view.

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