SQL Server DELETE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the DLELETE statement in SQL to remove records from the table.

What is DELETE clause in SQL Server?

The DELETE statement is used to remove one or more records (row of information also called tuple) which are not required anymore from a table.

Operation

The DELETE statement by itself operates on the whole table and will delete every record in the table. As such it is usually used with the WHERE clause to restrict its scope of operations and delete only those records which are returned by the WHERE condition. Apart from the WHERE condition the scope of operation of a DELETE statement can be defined by the TOP clause which takes an integer or percentage value as argument and deletes the appropriate number of records.

Syntax

The basic syntax of SQL Server DELETE statement is as follows.

DELETE [(TOP value) | ((value) PERCENT)]
FROM table_name
WHERE condition;

In this syntax,

• DELETE – SQL keyword to remove records from a table.
• TOP – SQL keyword indicating values from the top of the table.
• value – an integer value.
• PERCENT – keyword indicating that the preceding value is to be considered as a percentage value.
• FROM – SQL keyword to specify the object of the query (i.e. the table or schema).
• WHERE  condition(s) –    Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.

SQL Server DELETE Clause Examples

Let us go through a few examples to understand how the DELETE statement is used in conjunction with TOP and WHERE to remove records from a table. Suppose we have a table called employees which holds the records of the company’s employees. We will use this sample table as the reference tables in the examples that follow.

id	name	sex	age	doj	salary	div
203	Patricia Smith	F	29	11/11/2018	55000	302
207	Jeff Gossard	M	31	1/12/2017	66000	302
208	Melissa Mckagan	F	33	12/12/2018	65000	302
209	Jack Crow	M	31	1/10/2018	58000	302
210	Samuel Knight	M	29	7/14/2018	58000	302
201	Alicia Wells	F	23	9/1/2017	30000	301
202	Patrick Christ	M	24	9/9/2017	30000	301
204	Lauren Smith	F	25	3/23/2019	30000	301
205	Richard Anderson	M	25	3/13/2019	30000	301
206	David Mathews	M	27	4/24/2019	30000	301

1)  SQL Server DELETE – with TOP and PERCENT examples

First, we will check out how we can delete select rows from a table with the TOP keyword. The following queries both use TOP to delete the specified amount of records from the top of the table. The first query specifies the value 2 to delete the top 2 records from the table. The second query specifies 20 percentage to delete the 20 % records (which is basically the same as 20% of 10 records is 2 records).

delete top 2 from employees;

delete top (20) percent from employees;

We can check the result by running a SELECT query which will show that the top 2 records have been deleted and only 8 out of 10 records remain.

id	name	Sex	age	doj	salary	div
208	Melissa Mckagan	F	33	12/12/2018	65000	302
209	Jack Crow	M	31	1/10/2018	58000	302
210	Samuel Knight	M	29	7/14/2018	58000	302
201	Alicia Wells	F	23	9/1/2017	30000	301
202	Patrick Christ	M	24	9/9/2017	30000	301
204	Lauren Smith	F	25	3/23/2019	30000	301
205	Richard Anderson	M	25	3/13/2019	30000	301
206	David Mathews	M	27	4/24/2019	30000	301

2) SQL Server DELETE – with WHERE example

Now we will check how the WHERE clause is used with delete records from a table. The following query is an example of the same where employee records whose salary does not match the specified amount are deleted from the table.

delete from employees
where salary!=30000;

After deletion we can check the result by running a SELECT query which will show below output. Note that all employees have salary 30000.

id	name	sex	age	doj	salary	div
201	Alicia Wells	F	23	9/1/2017	30000	301
202	Patrick Christ	M	24	9/9/2017	30000	301
204	Lauren Smith	F	25	3/23/2019	30000	301
205	Richard Anderson	M	25	3/13/2019	30000	301
206	David Mathews	M	27	4/24/2019	30000	301