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.
