SQL Server 2005: Deleting multiple rows with a single query

Question

This should be a straightforward question, but I haven't found a clear answer yet. Does anyone know how to delete multiple rows from a single table in SQL Server 2005, using a single query? I wondered if it might just be the opposite of inserting multiple rows, using the UNION ALL method. So would this work? :

DELETE FROM Table (Name, Location)
SELECT 'Name1', 'Location1'
UNION ALL
SELECT 'Name2', 'Location2'
UNION ALL
SELECT 'Name3', 'Location3'
etc...

Thanks.

EDIT: I should point out that this is a link table that I'm trying to remove records from. There is no primary key, and any of the columns' values may repeat. So I need to be sure that both columns contain a certain value for the same record before I remove the record. Thanks again.

Answer 1

You could try this:

DELETE FROM YourTable
WHERE (Name = 'Name1' AND Location = 'Location1')
OR (Name = 'Name2' AND Location = 'Location2')
OR (Name = 'Name3' AND Location = 'Location3')

Answer 2

DELETE FROM T
FROM YourTable T
INNER JOIN (
SELECT 'Name1' AS Name, 'Location1' AS Location
UNION ALL
SELECT 'Name2', 'Location2'
UNION ALL
SELECT 'Name3', 'Location3'
) T2
ON T2.Name = T.Name
AND T2.Location = T.Location

Answer 3

Using a CTE worked for me - much easier than using ORs and brackets:

WITH del AS (
  SELECT 'Name1' AS nam, 'Location1' AS loc
  UNION ALL
  SELECT 'Name2', 'Location2'
  UNION ALL
  SELECT 'Name3', 'Location3')
DELETE FROM CLASSES 
 WHERE EXISTS(SELECT NULL
               FROM del d 
              WHERE d.name = name
                AND d.loc = location)

You can't define a table alias for the table in a delete statement; any column references without a table alias could be assumed to relate to the only table without one, but it depends on scope too.