SQL Delete Query

Marcos27
Marcos27 used Ask the Experts™
on
Hello Experts -

Very simple question. I want to delete records from my Subtask table, but only records from a certain product line, which is recorded in a related Incident table. A record from the Incident table may or may not have an associated record from the Subtask table, and it could have several. The tables are linked by common ID, as noted in the query below.

Here is my select statement:

SELECT  *
      FROM Subtasks
      Join Incident on Subtasks.StandardLongInteger001=Incident.IncidentID
      And Incident.StandardText002 not in ('Product1', 'Product2', 'Product3')

Will the following delete statement work?

DELETE Subtasks
WHERE Exists
(
FROM Subtasks
      Join Incident on Subtasks.StandardLongInteger001=Incident.IncidentID
      And Incident.StandardText002 not in ('Product1', 'Product2', 'Product3')
)

If this is not correct, please point me in the right direction? Thank you!

- Mark
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
< Knee-jerk reaction.  Deletes can have JOINs.  HOLY GOD MAKE A BACKUP BEFORE EXECUTING THIS >

DELETE
FROM Subtasks s
   JOIN Incident i on s.StandardLongInteger001=i.IncidentID
      And i.StandardText002 not in ('Product1', 'Product2', 'Product3')

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project. -Jim

btw, in case it helps, check out an article I wrote on SQL Server Delete Duplicate Rows

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial