SQL Server: DELETE Statement

In this post explains how to use the DELETE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DELETE statement is used to delete a single record or multiple records from a table in SQL Server.

Syntax

In the simplest form, the syntax for the DELETE statement in SQL Server (Transact-SQL) is:

DELETE FROM table
[WHERE conditions];

However, the full syntax for the DELETE statement in SQL Server (Transact-SQL) is:

DELETE [ TOP (top_value) [ PERCENT ] ]
FROM table
[WHERE conditions];

Parameters or Arguments

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted.

TOP (top_value)

Optional. If specified, it will delete the top number of rows in the result set based on top_value. For example, TOP(10) would delete the top 10 rows matching the delete criteria.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a top_value percentage of the total result set (as specfied by the PERCENT value). For example, TOP(10) PERCENT would delete the top 10% of the records matching the delete criteria.

Note

• You do not need to list fields in the SQL Server DELETE statement since you are deleting the entire row from the table.

Example - Using One condition

Let's look at a simple SQL Server DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM employees
WHERE first_name = 'Julie';

This SQL Server DELETE example would delete all records from the employees table where the first_name is 'Julie'.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete.

SELECT count(*)
FROM employees
WHERE first_name = 'Julie';

Example - Using Two conditions

Let's look at a SQL Server DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM employees
WHERE last_name = 'Johnson'
AND employee_id >= 80;

This SQL Server DELETE example would delete all records from the employees table where the last_name is 'Johnson' and the employee_id is greater than or equal to 80.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete.

SELECT count(*)
FROM employees
WHERE last_name = 'Johnson'
AND employee_id >= 80;

Example - Using TOP keyword

Let's look at a SQL Server DELETE example, where we use the TOP keyword in the DELETE statement.

For example:

DELETE TOP(3)
FROM employees
WHERE last_name = 'Johnson';

This SQL Server DELETE example would delete the first 3 records from the employees table where the last_name is 'Johnson'. If there are other records in the employees table that have a last_name value of 'Johnson', they will be unaffected by the DELETE statement.

Example - Using EXISTS Clause

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the SQL Server FROM clause when you are performing a delete, you can use the SQL Server EXISTS clause.

For example:

DELETE FROM employees
WHERE EXISTS
  ( SELECT *
    FROM contacts
    WHERE contacts.contact_id = employees.employee_id
    AND contacts.contact_id  100 );

This SQL Server DELETE example would delete all records in the employees table where there is a record in the contacts table whose contact_id is less than 100, and the contact_id matches the employee_id.

If you wish to determine the number of rows that will be deleted, you can run the following SQL Server SELECT statement before performing the delete.

SELECT COUNT(*)
FROM employees
WHERE EXISTS
  ( SELECT *
    FROM contacts
    WHERE contacts.contact_id = employees.employee_id
    AND contacts.contact_id  100 );