Home » SQL & PL/SQL » SQL & PL/SQL » How to get all child records from a table with multiple self referential integrity constraints? (merged 3) (11g)
How to get all child records from a table with multiple self referential integrity constraints? (merged 3) [message #676251] Thu, 23 May 2019 02:01 Go to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
As per the requirement i have to identified data from table t1. Once the data is identified i need to identify its child records as well.
I could easily identify its childs if there is only one self referential constraint.
like

select * from t1
start with c1=100
connect by prior c1=c2;

But My table has multiple self referential integrity constraints like below


create table t1(
c1 number pk,
c2 number references c1,
c3 number references c1,
c4 number references c1);


i have tried to identify the childs of each self RI separately. But the problem here is after identifying the childs i have to delete all the childs and parent records.
With the approach of identifying the self referential Integrity separately we may find one record is child in one self RI the same record might be parent in other Self RI. so while deleting we are facing child record found issue.

so i have used the below query to identify all the childs in one shot. But the query is running for hours and not getting finished.


select * from t1
start with c1=100
connect by prior c1=c2 or prior c1=c3 or prior c1=c4;


Please suggest a way to identify all the childs and delete them without constraint issues.

Thanks
How to get all child records from a table with multiple self referential integrity constraints? [message #676252 is a reply to message #676251] Thu, 23 May 2019 02:01 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
As per the requirement i have to identified data from table t1. Once the data is identified i need to identify its child records as well.
I could easily identify its childs if there is only one self referential constraint.
like

select * from t1
start with c1=100
connect by prior c1=c2;

But My table has multiple self referential integrity constraints like below


create table t1(
c1 number pk,
c2 number references c1,
c3 number references c1,
c4 number references c1);


i have tried to identify the childs of each self RI separately. But the problem here is after identifying the childs i have to delete all the childs and parent records.
With the approach of identifying the self referential Integrity separately we may find one record is child in one self RI the same record might be parent in other Self RI. so while deleting we are facing child record found issue.

so i have used the below query to identify all the childs in one shot. But the query is running for hours and not getting finished.


select * from t1
start with c1=100
connect by prior c1=c2 or prior c1=c3 or prior c1=c4;


Please suggest a way to identify all the childs and delete them without constraint issues.

Thanks
How to get all child records from a table with multiple self referential integrity constraints? [message #676253 is a reply to message #676251] Thu, 23 May 2019 02:04 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
As per the requirement i have to identified data from t1. Once the data is identified i need to identify its child records as well.

I could easily identify its childs if there is only one self referential constraint. As there are multiple im facing challenges.

create table t1(
c1 number pk,
c2 number references c1,
c3 number references c1,
c4 number references c1);

if it is one self referential i can easily identify like
select * from t1
start with c1=100
connect by prior c1=c2;
but as i have multiple i tried to use or condition but it is running for hours and not finishing
select * from t1
start with c1=100
connect by prior c1=c2 or prior c1=c3 or prior c1=c4;
suggest alternative solution to identify all its childs. Thanks in Advance.
Re: How to get all child records from a table with multiple self referential integrity constraints? [message #676254 is a reply to message #676253] Thu, 23 May 2019 02:10 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
i thought of using the below query
select * from t1
start with c1=100
connect by prior c1=c2 
union
select * from t1
start with c1=100
connect by prior c1=c3  
union
select * from t1
start with c1=100
connect by prior c1=c4;

But after identifying the childs i have delete all identified data. while deleting in bulk mode im getting errors like child records found. Upon debug we identified with this approach a record might be parent in one self RI and child in another self RI. so we have to build tree with all constraints . Please let me know if i missing something
Re: How to get all child records from a table with multiple self referential integrity constraints? [message #676257 is a reply to message #676251] Thu, 23 May 2019 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66688
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please suggest a way to identify all the childs and delete them without constraint issues.
No need to identify them just add ON DELETE CASCADE to your constraints definition and then delete the parent(s).

Re: How to get all child records from a table with multiple self referential integrity constraints? [message #676258 is a reply to message #676257] Thu, 23 May 2019 03:53 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
sorry to mention that We need to move this data to another db before deleting . All the identified rows will have to be moved and then purged.
Re: How to get all child records from a table with multiple self referential integrity constraints? (merged 3) [message #676263 is a reply to message #676251] Thu, 23 May 2019 09:52 Go to previous messageGo to next message
EdStevens
Messages: 1139
Registered: September 2013
Senior Member
Also asked on the OTN forum: https://community.oracle.com/thread/4271434
Re: How to get all child records from a table with multiple self referential integrity constraints? (merged 3) [message #676266 is a reply to message #676263] Thu, 23 May 2019 23:30 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
I myself posted on other forum too to get solution.. But No one give answer. people are more concerned with formatting it seems than actual query
Re: How to get all child records from a table with multiple self referential integrity constraints? (merged 3) [message #676277 is a reply to message #676266] Fri, 24 May 2019 08:06 Go to previous message
EdStevens
Messages: 1139
Registered: September 2013
Senior Member
rrcr wrote on Thu, 23 May 2019 23:30
I myself posted on other forum too to get solution.. But No one give answer. people are more concerned with formatting it seems than actual query
Yes, I know it was you. The people there are trying to get you to help them to to help you. Just like here, or any forum, you need to supply the requested information in a readable format. IT doesn't matter if you think you've provided the necessary information in a useful format. You are asking others to help you, so you need to give them what they ask for.

Also, the reason I posted a notice on both places of the posting in the other was to alert others of the cross posting. A lot of people are on both forums and don't want to get sucked into trying to deal with the same issue in two places.
Previous Topic: case if avec PL/SQL
Next Topic: can we call an sql script from the code of a stored procedure
Goto Forum:
  


Current Time: Mon Nov 18 17:31:21 CST 2019