Oracle SQL Merge to Insert and Delete But Not Update

Oracle sql merge to insert and delete but not update

No, you cannot delete rows that have not been updated by the merge command.

Here is documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Specify the DELETE where_clause to clean up data in a table while
populating or updating it. The only rows affected by this clause are
those rows in the destination table that are updated by the merge
operation
. The DELETE WHERE condition evaluates the updated value, not
the original value that was evaluated by the UPDATE SET ... WHERE
condition. If a row of the destination table meets the DELETE
condition but is not included in the join defined by the ON clause,
then it is not deleted. Any delete triggers defined on the target
table will be activated for each row deletion.

That means, that rows must be updated. Hovewer, you don't need to update all rows, after UPDATE use the same WHERE clause as you are using after DELETE

when matched then update set dummy=dummy
where a_value not in ('ace','afk')
delete
where a_value not in ('ace','afk')

Oracle MERGE won't insert only update

I think you've misunderstood how merges work. The source query (your select * from mytable where id = 0) is what is used to determine what data to update or insert.

If you don't have any records in mytable where the id = 0, you don't have anything to insert or update with.

I think what you had in mind was something like:

merge into mytable tgt
using (select 0 id, 1 loc_id, 'Lee' author from dual) src
on (tgt.id = src.id)
when matched then
update set tgt.loc_id = src.loc_id,
tgt.author = src.author
when not matched then
insert (tgt.id, tgt.loc_id, tgt.author)
values (src.id, src.loc_id, src.author);

Oracle - Procedure to merge with insert, update and delete

I think you can do this as a single statement as part of the data load. Let's assume that ORDER_A has been loaded (but I will comment on that later). Then you can define the result of the insert/update by doing a full outer join between ORDER_A and ORDER_B, and the use a CASE statement to project the "correct" value from ORDER_A or ORDER_B. Similarly you can project the FLAG_DELTED. It would look something like this. In this example, I am skipping the MD5, but this could be added if really needed - more on that later too

select
case
when ( b.order_id is null ) then a.order_id
else case when (
b.ORDER_ID != m.ORDER_ID or
b.ORDER_CODE != m.ORDER_CODE or
b.ORDER_STATUS != m.ORDER_STATUS or
b.ORDER_USER_ID != m.ORDER_USER_ID or
b.ORDER_DATE != m.ORDER_DATE or
b.DAT_UPDATE != SYSTIMESTAMP ) then b.order_id else a.order_id end
end as newOrder_id
, case when ( b.order_id is null ) then a.order_code
else case when (
b.ORDER_ID != m.ORDER_ID or
b.ORDER_CODE != m.ORDER_CODE or
b.ORDER_STATUS != m.ORDER_STATUS or
b.ORDER_USER_ID != m.ORDER_USER_ID or
b.ORDER_DATE != m.ORDER_DATE or
b.DAT_UPDATE != SYSTIMESTAMP ) then b.order_code else a.order_code end
end as newOrder_code
, case when ( b.order_id is null ) then a.order_status
else case when (
b.ORDER_ID != m.ORDER_ID or
b.ORDER_CODE != m.ORDER_CODE or
b.ORDER_STATUS != m.ORDER_STATUS or
b.ORDER_USER_ID != m.ORDER_USER_ID or
b.ORDER_DATE != m.ORDER_DATE or
b.DAT_UPDATE != SYSTIMESTAMP ) then b.order_status else a.order_status end
end as newOrder_status
/* etc... ( Repeat for all projected columns )
Then for the flag_deleted column */
, case when ( a.order_id is null ) then 1
when ( b.order_id is null ) then 0
else b.flag_deleted
end as newFlag_deleted
from Order_b b
full outer join Order_a a
on b.order_id = a.order_id

It may be possible that ORDER_A could be an external table, so then you would just need to prepend this with a

CREATE TABLE NEW_ORDER_A as select....

And then you have the results you need.

Where you a hemorrhaging performance in your example, is the update of ORDER_A. You are generating redo, undo and losing any compression benefits. You are also maintaining indexes, but indexes are not needed.
Assuming you have resources, you cab now use DIRECT PATH and parallelism, and this would scale pretty well.

Lastly, if you really do need the MD5, you need to add a special character between each column, otherwise is will be ambiguous. For example, the following woukd have the same MD5

COL1   COL2
AA BBB
AAB BB

Oracle Merge delete clause where data in target but NOT in source

In a MERGE delete Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted.

You may FULL JOIN the table with your TMP in the USING query and make use of a flag to delete the unmatched rows.

create or replace  PROCEDURE testProc(tmpPList IN P_REC_LIST)
AS
BEGIN
IF tmpPList.count > 0 THEN
MERGE INTO targetTable tt
USING ( with abc as
(
select * from TABLE(tmpPList)
)
select COALESCE(abc.attr1,t.attr1) as attr1,
abc.attr2,CASE WHEN abc.attr1 IS NULL
THEN 'Y' ELSE 'N' END
match_flag FROM abc
FULL JOIN targetTable t ON t.attr1 = abc.attr1
) tmp
ON (tt.Attr1 = tmp.attr1)
WHEN MATCHED THEN
UPDATE SET tt.Attr2 = tmp.Attr2
DELETE WHERE match_flag = 'Y' --This flag to identify the unmatched row
WHEN NOT MATCHED THEN
INSERT (Attr1)
VALUES (tmp.Attr1);
END IF;
END testProc;
/

Demo

Oracle Merge: When not matched then delete and insert

You cannot insert in the WHEN MATCHED THEN UPDATE clause. You can only DELETE here. And why do you need to delete and then insert if you can just update? Something like this:

merge into table_1 
using table_2 on (table_1.val1 = table_2.val1)
when matched then
update set table_1.val3 =
case when table_1.val2 = table_2.val2
then table_1.val3
else table_2.val3 end
when not matched then insert ...

From the comments below I suppose you need something like this:

1) There is no foreign keys which reference table_1

2) Disable the primary key val2 in table_1

3) update table_1 set val2 = null;

4) run merge

5) delete from table_1 where val2 is null;

6) Enable the primary key in table_1



Related Topics



Leave a reply



Submit