Update table data using two joins in SQL-CodePudding

I have 3 tables, and they are linked to each other. How can I upload data from table3 to table 1?

I tried this code:

UPDATE table1
SET t1.new_column = t3.old_data
FROM table1 t1
INNER JOIN table2 t2 ON t1.some_key = t2.some_key
INNER JOIN table3 t3 ON t2.some_key2 = t3.some_key2;

But it doesn't work. Thanks!

CodePudding user response：

Updating a join in Oracle is done using the join first and then the update. Thus the syntax looks like:

UPDATE (
select t1.new_column , t3.old_data
FROM table1 t1
INNER JOIN table2 t2 ON t1.some_key = t2.some_key
INNER JOIN table3 t3 ON t2.some_key2 = t3.some_key2
)
set new_column = old_data

The join must result in what is called "key preserved" data, ie, the rows that come back from the query must map one to one with the rows in the target table, otherwise you'll get an error.

For this reason, people often choose to rewrite their update as a MERGE statement.