CODEWITHSUNDEEP
sqlsql-server-2005sql-delete
MegaMatt
MegaMatt

Reputation: 23763

Deleting multiple rows with a single query

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...

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.

Upvotes: 6

Views: 18750

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 2

Will A
Will A

Reputation: 24988

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838216

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')

Upvotes: 11

Related Questions