469,111 Members | 1,964 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

Delete all records in tblA not matching in tblB

I'm hoping someone can tell me how to construct a stored procedure that
deletes all records in tblA not matching the PK in tblB

This gives me the recordset of all records in tblA with no matching
records in tblB (ID is the PK in tblB)

SELECT a.ID
FROM dbo.tblB b
RIGHT OUTER JOIN dbo.tblA a ON b.ID = a.ID
WHERE
b.ID IS NULL

thanks,
lq

Jul 23 '05 #1
2 1491
DELETE FROM tblA
WHERE NOT EXISTS
(SELECT *
FROM tblB
WHERE tblB.id = tblA.id)

(Untested. Make sure you have a current backup and test it out for
yourself first.)

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Thanks for that. I have never used NOT EXISTS before. lq

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by laurenq uantrell | last post: by
9 posts views Thread by Robert Schneider | last post: by
2 posts views Thread by Joe Del Medico | last post: by
3 posts views Thread by klufkee | last post: by
2 posts views Thread by Phil Stanton | last post: by
3 posts views Thread by Phil Stanton | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.