Solved

Deleting row with effecting Forigen Key

Posted on 2003-10-24
5
267 Views
Last Modified: 2008-02-01
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
Comment
Question by:dkilby
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9617486
is this a duplicate of your other question?

explain further with table / column / key details
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 250 total points
ID: 9617559
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9617576
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
 
LVL 3

Expert Comment

by:richardjb
ID: 9617577
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9617698
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question