asked on

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

Lowfatspread

is this a duplicate of your other question?

explain further with table / column / key details

ASKER CERTIFIED SOLUTION

namasi_navaretnam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

namasi_navaretnam

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

richardjb

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

Anthony Perkins

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