Deleting row with effecting Forigen Key

I have had to back fill a table with data because of forigen key constraits in other table but now have way too many useless row in the table.  Is there anyway of only deleting the rows that are do not have entries in other tables.  So I want to delete the rows that will not affect the rest of the dB.  

I had to merge a couple of dB together and now am trying to clean it up.

Thanks
dkilbyAsked:
Who is Participating?
 
namasi_navaretnamCommented:
If you are deleting from a parent table you need to identify the child tables first and then

DELETE ParentTable
where (NOT EXISTS (SELECT 1
             FROM ChildTable1
            WHERE ParentTable.FK = ChildTable1.FK)) AND
      (NOT EXISTS (SELECT 1
             FROM ChildTable2
            WHERE ParentTable.FK = ChildTable2.FK)) AND
      (NOT EXISTS (SELECT 1
             FROM ChildTable3
            WHERE ParentTable.FK = ChildTable3.FK)) AND

Etc

HTH

Namasi
0
 
LowfatspreadCommented:
is this a duplicate of your other question?

explain further with table / column / key details
0
 
namasi_navaretnamCommented:
Or you can try this
DELETE ParentTable
where FK_Col NOT IN (
    SELECT FK_Col
      FROM ChildTable1
    UNION
    SELECT FK_Col
      FROM ChildTable2
    UNION
    SELECT FK_Col
      FROM ChildTable3
)

HTH

Namasi
0
 
richardjbCommented:
Hi dkilby,

If it is like :-
create table a (a_id int primary key, ....)
create table b(b_id int primary key, a_id int foreign key references a(a_id),.....)

and you want to clear unnecessary rows from a then :-
delete from a where not exists ( select * from b where b.a_id = a.a_id)

Regards,  Richard
0
 
Anthony PerkinsCommented:
Please maintain your many old open questions:

1 07/31/2003 500 Urgent - Importing Data from Excel  Unlocked Visual Basic
2 07/31/2003 125 Selecting excel worksheet in SQL Stateme...  Unlocked Visual Basic
3 08/15/2003 500 SMTP + Emails Stuck in Queue  Unlocked Windows 2000
4 08/06/2003 125 Email listbox  Unlocked Visual Basic
5 09/10/2003 250 Input Mask  Unlocked JavaScript
6 09/18/2003 250 Visual Studio .NET + KMODE_EXCEPTION_NOT...  Unlocked Microsoft Visual Interdev
7 09/18/2003 500 Blue Screen Error  Unlocked Windows 2000

Thanks,
Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.