Home » SQL & PL/SQL » SQL & PL/SQL » Deleting referenced tables based on child table criteria (12.1.0.1 )
Deleting referenced tables based on child table criteria [message #689111] Tue, 26 September 2023 05:35 Go to next message
OraFerro
Messages: 426
Registered: July 2011
Senior Member
Good day to you,

I have a case where I need to delete both a child and a parent table records based on child table criteria. I cannot delete the parent first because of referential integrity, and if I deleted the child records first I will lose the condition based on which I want the delete the parent records.
In my case Sparent records that match a certain criterion in sChild need to be deleted together with those records in Schild that match the same criterion. The sample stored procedure starts with schild and of course it does not manage to delete any records from sparent.

following is my case:
create table sParent
  (
    pid number(2) primary key,
    dp date,
    v1 varchar2(10)
  );


create table sChild
  (
    sid number(2) primary key,
    ds date,
    v2 varchar2(10),
    fk_parent_id number(2) references sParent
  );



insert ALL
  into sParent(pid, dp, v1) values (1, to_date('01-01-2022','dd-mm-yyyy'), 5)
  into sParent(pid, dp, v1) values (2, to_date('01-02-2022','dd-mm-yyyy'), 15)
  into sParent(pid, dp, v1) values (3, to_date('01-03-2022','dd-mm-yyyy'), 51)
  into sParent(pid, dp, v1) values (4, to_date('01-01-2023','dd-mm-yyyy'), 25)
  into sParent(pid, dp, v1) values (5, to_date('01-02-2023','dd-mm-yyyy'), 53)
  into sParent(pid, dp, v1) values (6, to_date('01-03-2023','dd-mm-yyyy'), 45)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (10, to_date('01-08-2023','dd-mm-yyyy'), 7, 1)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (40, to_date('01-08-2023','dd-mm-yyyy'), 7, 4)
  into sChild(sid, ds, v2, FK_PARENT_ID) values (60, to_date('01-07-2023','dd-mm-yyyy'), 7, 6)
select * from dual;


CREATE OR REPLACE PROCEDURE P_DELETE_record
  (
    v2 in varchar2
  )
  AS
      D_ACQ_DUE DATE;
  BEGIN


      DELETE FROM schild WHERE v2 = v2;  -- I can start with child record but then I will not be able to delete any records from parent

      DELETE FROM sParent 
        WHERE pid IN 
              (
                SELECT FK_PARENT_ID FROM schild WHERE v2 = v2 -- this condition is only valid if child table exists.
              );

  END;

Regards,
Ferro
Re: Deleting referenced tables based on child table criteria [message #689112 is a reply to message #689111] Tue, 26 September 2023 06:08 Go to previous messageGo to next message
John Watson
Messages: 8903
Registered: January 2010
Location: Global Village
Senior Member
Could something on these lines work:

orclz>
orclz> set serverout on
orclz> declare
  2  type t is table of number;
  3  tab t;
  4  begin
  5  delete from schild where v2=7 returning fk_parent_id bulk collect into tab;
  6  for i in tab.first .. tab.last loop
  7  dbms_output.put_line(tab(i));
  8  end loop;
  9  end;
 10  /
1
4
6

PL/SQL procedure successfully completed.

orclz>
Re: Deleting referenced tables based on child table criteria [message #689113 is a reply to message #689111] Tue, 26 September 2023 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68550
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Besides the question (John already gave a solution), note that
DELETE FROM schild WHERE v2 = v2; 
will delete, if v2 is not NULL, ALL rows but those with v2 NOT NULL whatever is the passed value, and if this later is NULL, nothing.

Re: Deleting referenced tables based on child table criteria [message #689115 is a reply to message #689113] Wed, 27 September 2023 04:59 Go to previous messageGo to next message
OraFerro
Messages: 426
Registered: July 2011
Senior Member
@John Watson
Thanks a lot the example worked perfectly with me.

@Michel Cadot
I saw this coming and I see your point. Thanks.

Re: Deleting referenced tables based on child table criteria [message #689117 is a reply to message #689115] Wed, 27 September 2023 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68550
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you still want to have the same name for the column and the procedure parameter (very bad idea in my opinion but if you want to play a prank on your colleague), you can write:
DELETE FROM schild WHERE v2 = P_DELETE_record.v2; 
Re: Deleting referenced tables based on child table criteria [message #689121 is a reply to message #689115] Wed, 27 September 2023 08:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3261
Registered: January 2010
Location: Connecticut, USA
Senior Member
In general you should be locking child table. Nothing prevents other session from inserting row into child table with same parent key you are trying to delete. Another option would be making FK deferrable.

SY.
Re: Deleting referenced tables based on child table criteria [message #689128 is a reply to message #689121] Wed, 27 September 2023 23:31 Go to previous message
OraFerro
Messages: 426
Registered: July 2011
Senior Member
@Michel Cadot, tried it, it looks interesting thank you.
@Solomon Yakobson I will try to read about the other option you suggested "Another option would be making FK deferrable". Thank you.
Previous Topic: case statement giving an error
Next Topic: Count non workink days between two dates, from calendar table
Goto Forum:
  


Current Time: Thu Dec 07 00:26:44 CST 2023