{"id":11564,"date":"2022-07-31T13:07:32","date_gmt":"2022-07-31T11:07:32","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=11564"},"modified":"2023-11-08T16:58:47","modified_gmt":"2023-11-08T15:58:47","slug":"deleting-rows-with-merge","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2022\/07\/31\/deleting-rows-with-merge\/","title":{"rendered":"Deleting Rows With Merge"},"content":{"rendered":"\n

The merge<\/code> statement allows you to insert, update and delete rows in the target table in one go. This is great. However, the delete part does not work as expected. Is it a bug? No, it works exactly as documented. Nonetheless, I was not aware of this for years. Let’s take a look at this with an example.<\/p>\n\n\n\n

Setup<\/h2>\n\n\n\n

We create a table t<\/code> (target) with three rows and a table s<\/code> (source) with 4 rows. To log DML events we create some after row triggers on table t<\/code>.<\/p>\n\n\n\n

Setup<\/span><\/path><\/path><\/svg><\/span>
create<\/span> <\/span>table<\/span> <\/span>t<\/span> (<\/span><\/span>\n   id <\/span>integer<\/span>      <\/span>not null<\/span> <\/span>primary key<\/span>,<\/span><\/span>\n   c1 <\/span>varchar2<\/span>(<\/span>20<\/span>) <\/span>not null<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert into<\/span> t <\/span>values<\/span> (<\/span>1<\/span>, <\/span>'original 1'<\/span>);<\/span><\/span>\ninsert into<\/span> t <\/span>values<\/span> (<\/span>2<\/span>, <\/span>'original 2'<\/span>);<\/span><\/span>\ninsert into<\/span> t <\/span>values<\/span> (<\/span>3<\/span>, <\/span>'original 3'<\/span>);<\/span><\/span>\n<\/span>\ncreate<\/span> <\/span>table<\/span> <\/span>s<\/span> (<\/span><\/span>\n   id <\/span>integer<\/span>      <\/span>not null<\/span>,<\/span><\/span>\n   op <\/span>varchar2<\/span>(<\/span>1<\/span>)  <\/span>not null<\/span> <\/span>check<\/span> (op <\/span>in<\/span> (<\/span>'I'<\/span>, <\/span>'U'<\/span>, <\/span>'D'<\/span>)),<\/span><\/span>\n   c1 <\/span>varchar2<\/span>(<\/span>20<\/span>) <\/span>not null<\/span><\/span>\n);<\/span><\/span>\n<\/span>\ninsert into<\/span> s <\/span>values<\/span> (<\/span>1<\/span>, <\/span>'U'<\/span>, <\/span>'original 1'<\/span>);<\/span><\/span>\ninsert into<\/span> s <\/span>values<\/span> (<\/span>2<\/span>, <\/span>'U'<\/span>, <\/span>'changed 2'<\/span>);<\/span><\/span>\ninsert into<\/span> s <\/span>values<\/span> (<\/span>3<\/span>, <\/span>'D'<\/span>, <\/span>'deleted 3'<\/span>);<\/span><\/span>\ninsert into<\/span> s <\/span>values<\/span> (<\/span>4<\/span>, <\/span>'I'<\/span>, <\/span>'new 4'<\/span>);<\/span><\/span>\n<\/span>\ncreate or replace<\/span> <\/span>trigger<\/span> <\/span>t_ar_i<\/span> <\/span>after<\/span> <\/span>insert<\/span> <\/span>on<\/span> t <\/span>for<\/span> each <\/span>row<\/span><\/span>\nbegin<\/span><\/span>\n   sys.dbms_output.put_line(<\/span>'inserted id '<\/span> || :new.id);<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\ncreate or replace<\/span> <\/span>trigger<\/span> <\/span>t_ar_u<\/span> <\/span>after<\/span> <\/span>update<\/span> <\/span>on<\/span> t <\/span>for<\/span> each <\/span>row<\/span><\/span>\nbegin<\/span><\/span>\n   sys.dbms_output.put_line(<\/span>'updated id '<\/span> || :old.id);<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\ncreate or replace<\/span> <\/span>trigger<\/span> <\/span>t_ar_d<\/span> <\/span>after<\/span> <\/span>delete<\/span> <\/span>on<\/span> t <\/span>for<\/span> each <\/span>row<\/span><\/span>\nbegin<\/span><\/span>\n   sys.dbms_output.put_line(<\/span>'deleted id '<\/span> || :old.id);<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Insert, Update, Delete via Merge<\/h2>\n\n\n\n
Now, we can run this script:<\/p>\n\n\n\n
Merge #1<\/span><\/path><\/path><\/svg><\/span>
set<\/span> serveroutput <\/span>on<\/span> <\/span>size<\/span> <\/span>unlimited<\/span><\/span>\nmerge<\/span> <\/span>into<\/span> t<\/span><\/span>\nusing<\/span> s<\/span><\/span>\n   <\/span>on<\/span> (t.id = s.id)<\/span><\/span>\n <\/span>when<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>update<\/span><\/span>\n         <\/span>set<\/span> t.c1 = s.c1<\/span><\/span>\n      <\/span>delete<\/span><\/span>\n       <\/span>where<\/span> op = <\/span>'D'<\/span><\/span>\n <\/span>when<\/span> <\/span>not<\/span> <\/span>matched<\/span> <\/span>then<\/span><\/span>\n      <\/span>insert<\/span> (t.id, t.c1)<\/span><\/span>\n      <\/span>values<\/span> (s.id, s.c1);<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> t;      <\/span><\/span>\nrollback<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Output of Merge #1<\/span><\/path><\/path><\/svg><\/span>
updated id 1<\/span><\/span>\nupdated id 2<\/span><\/span>\nupdated id 3<\/span><\/span>\ndeleted id 3<\/span><\/span>\ninserted id 4<\/span><\/span>\n<\/span><\/span>\n4 rows merged.<\/span><\/span>\n<\/span><\/span>\n        ID C1                  <\/span><\/span>\n---------- --------------------<\/span><\/span>\n         1 original 1          <\/span><\/span>\n         2 changed 2           <\/span><\/span>\n         4 new 4               <\/span><\/span>\n<\/span><\/span>\nRollback complete.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The merge statement applied the insert, update and delete operation in the target table t<\/code>. The result in table t<\/code> is what we expect.<\/p>\n\n\n\n
However, when I look at the output of the DML triggers I do not like the following things:<\/p>\n\n\n\n