SQL - DELETE Query

The SQL DELETE Query is used to delete the existing records from a table.

Generally, if there exists a way to insert new data into any database object, then there will also be a way to remove unwanted data from the same object. Using SQL, insertion is performed on a table using the INSERT statement, while the deletion operation is performed using DELETE. Therefore, this statement is a part of Data Manipulation Language.

Usually, DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. In order to filter the records to be deleted, you can use the WHERE clause along with the DELETE statement.

Syntax

The basic syntax of the DELETE query with the WHERE clause is as follows −

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code has a query, which will DELETE a customer, whose ID is 6.

DELETE FROM CUSTOMERS
WHERE ID = 6;

Output

The output will be displayed as −

(1 row affected)

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would have the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Delete Multiple Rows

To delete multiple rows, specify multiple conditions in the WHERE clause which are satisfied by all the rows to be deleted. Let us look at an example below.

Example

From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

DELETE FROM CUSTOMERS
WHERE AGE > 25;

Output

The output will be displayed as −

(2 rows affected)

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

The query above will produce the following output −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Example

If you want to DELETE all the records from the CUSTOMERS table, you do not need to use the WHERE clause and the DELETE query would be as follows −

DELETE FROM CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record and will show the following output −

Msg 263, Level 16, State 1, Line 2
Must specify table to select from.