Delete Statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete Statement

Author

Topic

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2009-02-23 : 14:54:24

How would I write a delete statement that would delete the same rows that are being returned by this select statement?

SELECT *
FROM Task LEFT JOIN Incident ON Task.ParentLink_RecID = Incident.RecId
WHERE (((Incident.RecId) Is Null));

I know that you can't use a join in a delete statement but I'm not quite sure how to write it.

Can anyone please help me out?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-23 : 15:03:59

Yes you can use a join in a delete statement.

DELETE t
FROM Task t
LEFT JOIN Incident i
ON t.ParentLink_RecID = i.RecId
WHERE i.RecId IS NULL

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 15:10:23

Or even this:

Delete M
from
(SELECT t.*
FROM Task t LEFT JOIN Incident I ON t.ParentLink_RecID = I.RecId
WHERE I.RecId Is Null)M

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-23 : 15:14:10

Well there are several ways to skin a cat. The first solution provided is correct and shorter.

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 15:45:06

quote:
Originally posted by tkizer

Well there are several ways to skin a cat. The first solution provided is correct and shorter.

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

Subscribe to my blog

I know that. I am just showing the other way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-23 : 15:51:06

Test

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources