Solved

Deleting row with effecting Forigen Key

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

861 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

23 Experts available now in Live!

Get 1:1 Help Now