DELETE...FROM...WHERE...NOT IN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DELETE...FROM...WHERE...NOT IN

Author

Topic

rodcon
Starting Member

1 Post

Posted - 2008-04-10 : 21:32:57

I am trying to delete a specific record where it does not exist in another related table
ex. delete a registration if it does not have an associated invoice

ALTER PROCEDURE
dbo.DeleteRegistrationByRegistrationID(@registrationID int)
As
Delete
@registrationID
From
registrations
Where
registrations.registrationID
NOT IN (
SELECT
invoices.registrationID
FROM
invoices
WHERE
invoices.registrationID = @registrationID);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 22:37:10

Try this:

DELETE FROM Registrations r
WHERE NOT EXISTS (SELECT * FROM Invoices i WHERE r.registrationID = i.registrationID) AND
registrationID = @registrationID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources