Solved

Deleting row with effecting Forigen Key

Posted on 2003-10-24
5
265 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now