Solved

Finding Orphan DB Records

Posted on 2014-04-03
4
419 Views
Last Modified: 2014-04-03
From the "Poor DB Design" department:  I have a database with an "Events" table and a "RaceData" table.  Every record in the "RaceData" table should be tied to the "Events" table via the "EventsID" FK-PK relationship.  Here is the problem:  I think I have some orphans in the "RaceData" table.  How can I find and delete them?
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 167 total points
ID: 39975722
Hi,

Find..
select 
	rc.*
from 
	RaceData rc
left join 
	[Events] e
on
	rc.EventsID = e.EventsID
where
	e.EventsID is null

Open in new window


Delete..
delete
from 
	RaceData rc
left join 
	[Events] e
on
	rc.EventsID = e.EventsID
where
	e.EventsID is null

Open in new window

0
 
LVL 1

Assisted Solution

by:LogisticsOne
LogisticsOne earned 167 total points
ID: 39975727
You can do a
SELECT * FROM RaceData R
WHERE not exists (select * from Events E where E.EventsId = R.EventsID)

This will match all your Events ID's with the RaceData ID's and then compare that to all the ID's in the RaceData table and return values that are in the RaceData table, but no in the Events table.

(or reverse if if you're checking for orphans in the Events table that don't have a match in the RaceData)

I'm not sure about the performance hit, but I use something similar to this on some tables that are around 2million records each and it just takes a few seconds.
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 166 total points
ID: 39975733
I would recommend first looking at what records you're talking about by running this:
SELECT rd.*
  FROM RaceData AS rd
 WHERE NOT EXISTS (SELECT e.EventsID
FROM EVENTS AS e
WHERE rd.EventsID = e.EventsID)

Open in new window


If you see anything other than exactly what you want to delete, DO NOT RUN THE BELOW DELETE.

After you verify what you see returned is exactly what you want deleted, run the delete below:
DELETE FROM RaceData AS rd
WHERE NOT EXISTS 
(SELECT e.EventsID
 FROM Events As e
 WHERE rd.EventsID = e.EventsID)

Open in new window

0
 

Author Closing Comment

by:Bob Schneider
ID: 39975981
Great solutions.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

615 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