CODEWITHSUNDEEP
oracle-databasemerge
Martin Schapendonk
Martin Schapendonk

Reputation: 13536

Can MERGE update on both tables?

In Oracle, I use a MERGE statement like:

merge into live_table l
using (
  select *
  from staging_table
  where merged = 'N'
  ) s
on (i.id = s.id)
when matched then
  update l.description = s.description
when not matched then
  insert (l.id, l.description)
  values (s.id, s.description)
;

The staging table also contains a "merged" flag, which I want to update to 'Y' after the merge.

I thought I could combine this with an extra update (after the merge) like:

update staging_table
set merged = 'Y'
where id in (select id
  from live_table)
;

but this also updates the flag on records that have already been merged in previous transactions. A workaround is to write this as a loop that handles the insert/updates one by one, but that would defeat the ease of the MERGE statement.

How can I update the merged flag in such a way that I'm sure that only those rows affected by the merge statement are updated?

Upvotes: 1

Views: 209

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

In this instance I would use a PL/SQL block, and use bulk collect + bulk binds to do the needed queries and updates/inserts.

Upvotes: 3

Related Questions