Merge into Table1 using table2, table3 on (multiple conditions)? Missing syntax

Question

I have table1, table2 and table3, they have a join condition that relates between them. suppose it is the (ID) column.

So the question is,

in the case of using the merge statement, is it possible to construct the syntax to be like this:

Merge into Table1 

using table2 , table3
on (table1.ID = Table2.ID , Table2.ID = Table.ID) 
when match then 
update --(definitly table1)
where
table1.something between table2.something and table2.something -- whatever :)
when not match then 
do_nothing  --I think I should type NULL here

if this syntax is wrong, how should I call two tables and using them to update a row in table1?

not sure why you want to use a merge, especially as you don't want to act on 'not match'. my suggestion would be an update with a correlated subquery. google 'oracle update correlated subquery' and see here for a simple example: http://www.learncertification.com/study-material/update-and-delete-rows-using-correlated-subquery-in-oracle or here: https://searchoracle.techtarget.com/answer/Correlated-update-in-Oracle HTH KR — Peter, Dec 13 '18 at 19:50

score 3 · Answer 1 · answered Dec 13 '18 at 23:07

how should I call two tables and using them to update a row in table1?

This can be achieved in several ways in Oracle :

correlated subquery
inline view
merge query

The following code gives a raw, commented example of the third solution (merge statement). As you did not show us your exact SQL attempt and the structure of your tables, you will have to adapt this to your exact use case :

MERGE INTO table1 target
-- prepare the dataset to use during the UPDATE
USING (
    SELECT 
        -- following fields will be available in the UPDATE
        t1.id,
        t2.foo,
        t3.bar
    FROM
        -- JOIN conditions between the 3 tables
        table1 t1
        INNER JOIN table2 t2 on t2.id = t1.id
        INNER JOIN table3 t3 on t3.id = t1.id
    WHERE
       -- WHERE clause (if needed)
        t1.zoo = 'blah'
    ) source
-- search records to UPDATE 
ON  (target.id = source.id)
WHEN MATCHED THEN 
    UPDATE SET
        -- UPDATE table1 fieds
        target.value1 = source.foo,
        target.value2 = source.foo
;

Note : while this query makes use of the Oracle MERGE statement, it conceptually does not implement a real merge operation. The concept of a merge is an update/insert query, whereas this query only does an update, and ignores the insert part. Still, this is one of the simplest way to perform such a correlated update in Oracle...

Merge into Table1 using table2, table3 on (multiple conditions)? Missing syntax

1 Answers1