Deleting Records from multiple tables with the SQL Query-CodePudding

I have a staging table in Oracle DB where the data is loaded and table name is pc_stg_auth. It has 4 PKs. I Have to match these 4 PKs with another table called autho_activity_msc and if the PKs match then I have to delete the record from both the staging table called pc_stg_auth as well as from autho_activity_msc.

I am using below query but it is giving me a syntax error:

delete  autho_activity_msc , pc_stg_auth from autho_activity_msc inner join pc_stg_auth 
where autho_activity_msc.reference_number =  pc_stg_auth.reference_number AND
    autho_activity_msc.external_stan =   pc_stg_auth.external_stan   AND
    autho_activity_msc.routing_code =    pc_stg_auth.routing_code   AND
    autho_activity_msc.capture_code =    pc_stg_auth.capture_code;
    commit;

Below is the eror:

line 1: ORA-00933: SQL command not properly ended

please help or suggest if there is a simpler way to achieve it.

CodePudding user response：

Oracle does not support that syntax.

You need to use multiple DELETE statements. You can delete the matched rows from one table and collect the matched keys into collections and then loop through the collections and delete all the rows in the second table:

DECLARE
  rns SYS.ODCINUMBERLIST;
  ess SYS.ODCINUMBERLIST;
  rcs SYS.ODCINUMBERLIST;
  ccs SYS.ODCINUMBERLIST;
BEGIN
  DELETE FROM pc_stg_auth
  WHERE (reference_number, external_stan, routing_code, capture_code)
        IN (SELECT reference_number, external_stan, routing_code, capture_code
            FROM   autho_activity_msc)
  RETURNING reference_number, external_stan, routing_code, capture_code
  BULK COLLECT INTO rns, ess, rcs, ccs;
  
  FORALL i IN 1 .. rns.COUNT
    DELETE FROM autho_activity_msc
    WHERE reference_number = rns(i)
    AND   external_stan    = ess(i)
    AND   routing_code     = rcs(i)
    AND   capture_code     = ccs(i);
  
  COMMIT;
END;
/

db<>fiddle here