Creating a Procedure using Merge Into’ but getting Syntax Errors SQL Developer/DBVisualizer

Question

I have 5 tables in total. I wrote the initial sub query to gather the proper Pace IDs/Product IDs according to the Master Product ID. However, when I go to join to the Status/Record Tables in my procedure, I am getting errors.

The Master Table has the PACE ID.
The Pace Table has the Pace ID/Product ID:

PACE ID	PRODUCT ID
12345	776756
12347	776758

Master Pace Mapping Table has the Product ID/Master Product ID:

Product ID	Master Product ID
776756	112987
776758	112987

The Status Table has the PACE ID/STATUS/ACCOUNT ID:

PACE ID	STATUS	ACCOUNT ID
12345	SUBMITTED	A4562075
12345	SUBMITTED	A7653082
12345	NOT SUBMITTED	A2340563
12347	SUBMITTED	A3782257

The Record table is the table we need to delete from which has the Pace ID/ Account ID / TO FILE columns:

PACE ID	ACCOUNT ID	TO FILE
12345	A4562075	Y
12345	A7653082	Y
12345	A2340563	Y
12347	A3782257	Y

I need to gather the product IDs using the below sub query which joins to the Master ID, see if the status for each account/product id is Submitted or Not Submitted, then delete from the Record table if we already had submitted.

I am getting syntax errors when trying to incorporate everything within the procedure and am unsure how to proceed. Would anyone be able to explain what I would need to do?

Sub Query that works correctly:

select ma.pace_id, cc.product_id
from prod.master ma 
join prod.pace cc on cc.pace_id = ma.pace_id
where cc.product_id in(

select rcm.product_id
from prod.master c 
join prod.pace ccm on ccm.pace_id = c.pace_id
join prod.master_pace_mapping rcm on rcm.master_product_id = ccm.product_id and rcm.delete_date is null
where ccm.pace_id = 12345
     );

Within the Procedure that is not working:

CREATE OR REPLACE PROCEDURE “PROD”.”PARTICIPATION”
(P_PACE_ID NUMBER,
 P_CLIENT VARCHAR2)

MERGE INTO FRT.RECORD
USING(

     select ma.pace_id, cc.product_id
     from prod.master ma 
     join prod.pace cc on cc.pace_id = ma.pace_id
     where cc.product_id in(
     
     select rcm.product_id
     from prod.master c 
     join prod.pace ccm on ccm.pace_id = c.pace_id
     join prod.master_pace_mapping rcm on rcm.master_product_id = ccm.product_id and rcm.delete_date is null
     where ccm.pace_id = P_PACE_ID
     ) A JOIN SELECT S.ACCOUNT_ID, S.STATUS FROM PROD.STATUS S WHERE S.PACE_ID = A.CASE_ID;

WHEN STATUS = ‘SUBMITTED’ THEN DELETE FROM PROD.RECORD

Littlefoot · Accepted Answer · 2024-08-15 07:12:12Z

1

Merge can't delete rows; it updates or inserts, which means that you'll have to redesign the procedure. BTW, code you posted is obviously invalid.

A is joined to SELECT statement (can't be) which is then terminated by semi-colon, but
MERGE continues with the WHEN clause (invalid, though).

Quite a mess, and I can't figure out what you meant to say by that.

Furthermore, you declared p_client parameter but it is never used; what is its purpose?

Also, why are you checking master, pace and master_pace_mapping? To me, it seems that status contains everything you need. If all these tables are referenced by each other in various ways, good; but - from my point of view (regarding task you're trying to perform, which is deleting rows from the record table), rows in status and record can't exist without their parent rows in master/pace/master_pace_mapping (so why including them in query?).

Therefore, why not just

create or replace procedure p_del_rec (p_pace_id in status.pace_id%type)
is
begin
    delete from record r
    where (r.pace_id, p.account_id) in 
         (select s.pace_id, s.account_id
          from status s
          where s.pace_id = p_pace_id
            and s.status = 'SUBMITTED'
         );
end;

answered Aug 15, 2024 at 7:12

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul W Over a year ago

MERGE can delete, it's an (inelegant) option within the update subroutine

Littlefoot Over a year ago

Cool. @Paul, I like your answer. Never used it that way. Excellent.

Paul W · Accepted Answer · 2024-08-15 14:49:23Z

MERGE can be used to delete:

MERGE INTO record tgt
USING (SELECT .... [whatever you need]) src
  ON (src.pace_id = tgt.pace_id AND
      src.account_id = tgt.account_id)
 WHEN MATCHED THEN UPDATE tgt.to_file = src.to_file  -- dummy update, required by syntax 
                   DELETE WHERE 1=1 -- something that always is true

Why would you ever do this rather than a normal DELETE? Because DELETE statements can only be joined to other tables in subqueries which are hard to control, have extremely limited ability to hint, won't typically use parallelism unless in PDML mode, and tend to use nested loops when hash joins are preferrable. By placing all your complex join logic inside the USING clause of a MERGE, you have full control over how the joins happen, and can usually get the best performance that way if you are dealing with a large data volume and want parallel hash joins to predominate or don't have things properly indexed for a decent nested loops. These situations are common in a data-warehouse environment.

But, for your average few-rows delete operation, don't bother. It's easier and clearer to write a simple DELETE.

Collectives™ on Stack Overflow

Creating a Procedure using Merge Into’ but getting Syntax Errors SQL Developer/DBVisualizer

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related