Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 883
MegaMatt Avatar asked Jul 17 '10 18:07

MegaMatt


People also ask

How do I delete multiple rows in a table?

If you want to remove more than one row or column, select a cell in each row or column you want to delete. Under Table Tools, click Layout, and then click either Delete Row or Delete Column.

How do I delete multiple rows in Excel?

If you want to delete multiple rows or columns at the same time, you can use the Ctrl+Shift+- shortcut. Simply select the rows or columns you want to delete, and then press the Ctrl+Shift+- keys on your keyboard. All of the selected rows and columns will be deleted.

Can we delete records from multiple tables in a single query SQL Server?

The syntax also supports deleting rows from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword: DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id; What if you want to delete nonmatching rows?


2 Answers

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')
like image 110
Mark Byers Avatar answered Sep 29 '22 09:09

Mark Byers


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.

like image 37
OMG Ponies Avatar answered Sep 29 '22 09:09

OMG Ponies