September 7, 2005 at 5:12 pm
I have three tables,
1. Imports (field of XNumber)
2. General Info (fields of XNumber, YNumber)
3. Milestones (field of YNumber)
and what I need to do is delete records from the Milestones table where the Imports key/field doesn't match. So, basically delete all unmatched records in Milestones where there's no match with Imports.
The problem is that I've got to use General Info as a join, or parent table.
The schema looks like (at least I'm trying this way):
GeneralInfo.YNumber TO Milestones.YNumber
GeneralInfo.XNumber TO Imports.XNumber
YNumber in the GeneralInfo table is the unique identifer, and I do have this to work with on a select or delete statement.
There can be multiple same YNumbers in Milestones, and multiple same XNumbers in Imports.
September 7, 2005 at 9:15 pm
Try this:
delete M
from Milestones M
where not exists (select *
from GeneralInfo join Imports
on GeneralInfo.XNumber = Imports.XNumber
where GeneralInfo.YNumber = M.YNumber)
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
September 8, 2005 at 12:26 am
For big tables better to use joins:
delete M
from Milestones M
left join GeneralInfo on GeneralInfo.YNumber = M.YNumber
left join Imports on GeneralInfo.XNumber = Imports.XNumber
where Imports.XNumber IS NULL
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply