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
Table: employees
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.