SQL – DELETE Query

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

Generally, if there is a way to

insert new data into any database object, there will also be a way to delete unwanted data from the same object. With SQL, the insertion is performed in a table by using the INSERT statement, while the delete operation is performed by using DELETE. Therefore, this statement is part of the data manipulation language.

Typically, the DELETE statement can be used to delete multiple rows from a single table and records in multiple tables. To filter the records to be deleted, you can use the WHERE clause together 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 the AND or OR operators.

Example

Consider that the CLIENTS table has 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 | Chaitali | 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 client, whose ID is 6.

REMOVE FROM CLIENTS WHERE ID = 6;

Output

The output will be displayed as −

(1 row affected)

Verification

To check whether the records have been deleted from the table or not, let’s recover the modified table. For that, use the SELECT query below – SELECT * of

customers;

The CLIENTS table would now 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 | Chaitali | 25 | Mumbai | 6500,00 | | 5 | Hardik | 27 | Bhopal | 8500,00 | | 7 | Muffy | 24 | Indore | 10000,00 | +-+-+-+-+-+

Delete multiple rows

To delete multiple

rows, specify several conditions in the WHERE clause that satisfy all rows to delete. Let’s look at an example below.

Example

In the same Customers table, let’s try to delete records for customers over the age of 25.

REMOVE FROM CUSTOMERS WHEN > 25 YEARS;

Output

The output will be displayed as −

(2 rows affected)

Verification

To check whether the records have been deleted from the table or not, let’s recover the modified table. For that, use the SELECT query below – SELECT * of

customers;

The above query will produce the following result −

+-+-+-+-+-+ | ID | NAME | AGE | ADDRESS | SALARY | +-+-+-+-+-+ | 2 | Khilan | 25 | Delhi | 1500,00 | | 3 | Kaushik | 23 | Kota | 2000,00 | | 4 | Chaitali | 25 | Mumbai | 6500,00 | | 7 | Muffy | 24 | Indore | 10000,00 | +-+-+-+-+-+

Example

If you want to DELETE

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

OF CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To check whether the records have been deleted from the table or not, let’s recover the modified table. For that, use the SELECT query below – SELECT * of

customers;

Now, the CLIENTS table would have no record and will show the following output:

Msg 263, Level 16, State 1, Line 2 You must specify the table from which to select.

Contact US