Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Deleting Multiple Rows with One DELETE Statement

By: FYIcenter.com

(Continued from previous topic...)

How To Delete Multiple Rows with One DELETE Statement?

You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the fyi_links table:

-- view rows to be deleted
SELECT id, url, notes, counts FROM fyi_links 
   WHERE id > 300
GO
id    url               notes   counts
801	moc.retneciyf.ved	Wrong	1202
802	moc.retneciyf.abd	Wrong	1204
803	moc.retneciyf.aqs	Wrong	1206

-- delete multiple rows
DELETE FROM fyi_links WHERE id > 300
GO
(3 row(s) affected)

-- try to view the deleted row
SELECT id, url, notes, counts FROM fyi_links 
   WHERE id > 300
GO
no rows

(Continued on next topic...)

1. What Are DML (Data Manipulation Language) Statements?
2. How To Create a Testing Table with Test Data?
3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
4. How To Use Column Default Values in INSERT Statements?
5. How to provide column names in INSERT Statements?
6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
7. How To Insert Multiple Rows with One INSERT Statement?
8. How To Update Values in a Table with UPDATE Statements?
9. How To Update Multiple Rows with One UPDATE Statement?
10. How to use old values to define new values in UPDATE statements?
11. Is the Order of Columns in the SET Clause Important?
12. How To Use Values from Other Tables in UPDATE Statements?
13. What Happens If the UPDATE Subquery Returns No Rows?
14. What Happens If the UPDATE Subquery Returns Multiple Rows?
15. How To Delete an Existing Row with DELETE Statements?
16. How To Delete Multiple Rows with One DELETE Statement?
17. How To Delete All Rows with DELETE Statements?
18. How To Delete All Rows with TRUNCATE TABLE Statement?