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 |
|
OraFerro
Messages: 433 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 |
John Watson
Messages: 8938 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>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:25:29 CDT 2024
|