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.

idnamesexagedojsalarydiv
203Patricia SmithF2911/11/201855000302
207Jeff GossardM311/12/201766000302
208Melissa MckaganF3312/12/201865000302
209Jack CrowM311/10/201858000302
210Samuel KnightM297/14/201858000302
201Alicia WellsF239/1/201730000301
202Patrick ChristM249/9/201730000301
204Lauren SmithF253/23/201930000301
205Richard AndersonM253/13/201930000301
206David MathewsM274/24/201930000301
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.

idnameSexagedojsalarydiv
208Melissa MckaganF3312/12/201865000302
209Jack CrowM311/10/201858000302
210Samuel KnightM297/14/201858000302
201Alicia WellsF239/1/201730000301
202Patrick ChristM249/9/201730000301
204Lauren SmithF253/23/201930000301
205Richard AndersonM253/13/201930000301
206David MathewsM274/24/201930000301

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.

idnamesexagedojsalarydiv
201Alicia WellsF239/1/201730000301
202Patrick ChristM249/9/201730000301
204Lauren SmithF253/23/201930000301
205Richard AndersonM253/13/201930000301
206David MathewsM274/24/201930000301

Advertisement