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.
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.
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.
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?
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')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With