Sql to get orphans
Hi Experts,
The database is Oracle 11g
I have table Parent with column ( ID ,name)
The table Child has column ChildID , ParentID ,Name
sample data
Parent
P1 Parent1
P2 Parent2
Child
C1 P1 Child1
C2 P1 Child2
C3 P3 Child3
it is expected for each ChildID there has to be a corresponding
There are some orphaned rows in the table Child . In the sample data Child3 is an orphan because there is no P3 in Parent table
I need help in getting the Sql that can give the orphan children
The database is Oracle 11g
I have table Parent with column ( ID ,name)
The table Child has column ChildID , ParentID ,Name
sample data
Parent
P1 Parent1
P2 Parent2
Child
C1 P1 Child1
C2 P1 Child2
C3 P3 Child3
it is expected for each ChildID there has to be a corresponding
There are some orphaned rows in the table Child . In the sample data Child3 is an orphan because there is no P3 in Parent table
I need help in getting the Sql that can give the orphan children
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the exact error message?
Is p.uid an integer?
Is p.uid an integer?
You would need to put it in double quotes:
select p."UID" from parent p
The double quotes makes is case sensitive, so make sure it is upper case.
This is a total design flaw, you should never have column names with reserved words.
An alternate way to find duplicates:
Create an exception table by running this in SQL*Plus:
@?/rdbms/admin/utlexcpt
Then create a referential integrity constraint (something you should have to prevent this in the first place):
alter table child add foreign key (id) references parent("UID") exceptions into exceptions;
Then you can get all the invalid rows with this:
select * from child where rowid in (select row_id from exceptions);
Let Oracle do the work for you.
select p."UID" from parent p
The double quotes makes is case sensitive, so make sure it is upper case.
This is a total design flaw, you should never have column names with reserved words.
An alternate way to find duplicates:
Create an exception table by running this in SQL*Plus:
@?/rdbms/admin/utlexcpt
Then create a referential integrity constraint (something you should have to prevent this in the first place):
alter table child add foreign key (id) references parent("UID") exceptions into exceptions;
Then you can get all the invalid rows with this:
select * from child where rowid in (select row_id from exceptions);
Let Oracle do the work for you.
Do you have foreign key constraints on the child tables?
UID returns an integer that uniquely identifies the session user (the user who logged on).https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions211.htm
certainly not a good idea to use that as a column name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER