CODEWITHSUNDEEP
sqloracle-databasemerge
Meow Meow
Meow Meow

Reputation: 666

WHERE Statement in MERGE

Can someone explain how will work WHERE at the end of the MERGE statement if it has conditions on a target and a source table? For instance:

merge into target_table t
using source source_table s
on s.flield = t.field
when matched then update (...)
when not matched then insert (...)
where t.field != <value> and s.field != <value>

I can't get how will be resolved t.field because of the source strings are matching with no one string of target table in the when not matched clause.

I did some tests so it seems to me rows will be never inserted.

I want to know: any row will be inserted or not?

Upvotes: 0

Views: 612

Answers (1)

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

From the official Oracle documentation for MERGE statement:

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

To answer your question explicitly ("I want to know: any row will be inserted or not?"), the anwer is: NO.

Why? Because you have the wrong syntax.

You're using target table column (t.field) inside the merge_insert_clause, which is not allowed, and it should raise the ORA-38102: Invalid column in the INSERT WHERE Clause error: see this fiddle example!

From the docs:

The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false.

If you describe more precisely when do you want to insert and when do you want to update than I'll also edit my answer with further instruction how to do so.

I hope I helped!

Upvotes: 2

Related Questions