DELETE statement syntax:
DELETE FROM table_Name WHERE search_condition
table_Name – source table to delete records
search_condition – search filter to select only particular rows to delete.
Note: if you ignore the WHERE clause, all records will be deleted.
The following query deletes all rows from the User table because a WHERE clause is not used to limit the number of rows to delete.
DELETE FROM UserDetails
The following statment deletes only the rows which has the value 5 in the column ID.
DELETE FROM UserDetails Where ID=5
Delete Multiple Rows:
You can delete multiple rows from SQL Server Table by using suitable search condition. The following statement deletes the rows which has the value 1 0r 2 in the column ID.
DELETE FROM UserDetails Where ID=1 or ID=2
To make simple query, you can use IN in where clause to select multiple records.
DELETE FROM UserDetails Where ID IN ('1','2')
You can even delete rows from one table with a search condition which depends on different table.
DELETE FROM UserDetails Where ID IN (SELECT ID SomeOtherTable Where search_condition)
Delete All Rows from SQL Server Table:
You can delete all the records by ignoring where clause.
DELETE FROM UserDetails
But above Delete statement will be very slow because it works one row at a time. So you can use Truncate if it possible. Truncate delete all the rows from table, and it is faster than delete since it uses fewer system and transaction log resources than DELETE.
Truncate Table UserDetails
There are a few situations where truncate doesn’t work and may not suitable. See this article to know the difference between Delete vs Truncate.