• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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
0
dkilby
Asked:
dkilby
1 Solution
 
LowfatspreadCommented:
is this a duplicate of your other question?

explain further with table / column / key details
0
 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now