SQL Delete

Last update on April 30 2024 12:44:03 (UTC/GMT +8 hours)

SQL Delete records from a table

The SQL DELETE command is used to delete rows or records from a table.

Syntax:

DELETE FROM { table_name | ONLY (table_name) }
[{ WHERE search_condition | WHERE CURRENT OF cursor_name }]

Parameter:

Name	Description
table_name	Name of the table.
search_condition	specify a search condition (logical expression) that has one or more conditions.

Syntax diagram - DELETE STATEMENT

Some important questions on the SQL DELETE statement

What is the purpose of the DELETE statement in SQL?

The DELETE statement is used to remove one or more records from a table in a database.

How do you delete specific records from a table using the DELETE statement?

To delete specific records from a table, you include a WHERE clause in the DELETE statement with conditions that specify which records to remove based on column values.

Can you rollback a DELETE statement in SQL?

Yes, if the database management system supports transactions and if the DELETE statement is executed within a transaction, you can rollback the transaction to undo the effects of the DELETE operation.

What are some alternatives to using the DELETE statement for data removal?

Depending on the scenario, alternatives to using the DELETE statement include using the TRUNCATE TABLE statement (to remove all records from a table without logging individual deletions), using stored procedures with additional logic for validation and logging, or using soft delete mechanisms where records are marked as inactive rather than physically deleted.

What is the significance of the WHERE clause in a DELETE statement?

The WHERE clause in a DELETE statement is used to specify the conditions that must be met for records to be deleted. It allows for selective deletion of records based on specified criteria.

What happens if you execute a DELETE statement without a WHERE clause?

If you execute a DELETE statement without a WHERE clause, it will remove all records from the specified table, resulting in the deletion of all data.

How do you ensure data integrity when using the DELETE statement?

To ensure data integrity, you should always make sure to have proper backup procedures in place before executing DELETE statements, especially when dealing with critical data. Additionally, testing DELETE statements in a development or staging environment before executing them in a production environment can help prevent accidental data loss.

How do you delete records from multiple tables using a single DELETE statement?

To delete records from multiple tables using a single DELETE statement, you can use a subquery in the WHERE clause or specify joins between tables to establish relationships and delete records based on those relationships.

What are the components of a DELETE statement?

The main components of a DELETE statement include the DELETE keyword, the FROM clause specifying the table from which records will be deleted, and an optional WHERE clause to specify the conditions for deleting records.

What are the potential risks associated with executing a DELETE statement?

One potential risk is accidentally deleting important data if the WHERE clause is not properly specified. It's essential to double-check the conditions before executing the DELETE statement to avoid unintended data loss.

Table of Contents :

SQL deleting records with where
SQL Delete records using comparison operators
SQL delete records using IN operator
SQL delete records using BETWEEN operator
SQL delete records using LIKE operator

Sample tables associated with this page have shown bellow:

Sample table: daysorder

+---------+------------+----------------+------------+-----------+------------+-----------------+
| ORD_NUM | ORD_AMOUNT | ADVANCE_AMOUNT | ORD_DATE   | CUST_CODE | AGENT_CODE | ORD_DESCRIPTION |
+---------+------------+----------------+------------+-----------+------------+-----------------+
|  200107 |    4500.00 |         900.00 | 2008-08-30 | C00007    | A010       | SOD             |
|  200114 |    3500.00 |        1500.00 | 2008-08-15 | C00002    | A008       | SOD             |
|  200134 |    4200.00 |        1800.00 | 2008-09-25 | C00004    | A005       | SOD             |
|  200115 |    2000.00 |        1200.00 | 2008-02-08 | C00013    | A013       | SOD             |
|  200101 |    3000.00 |        1000.00 | 2008-07-15 | C00001    | A008       | SOD             |
|  200128 |    3500.00 |        1500.00 | 2008-07-20 | C00009    | A002       | SOD             |
|  200132 |    4000.00 |        1500.00 | 2008-08-15 | C00013    | A013       | SOD             |
+---------+------------+----------------+------------+-----------+------------+-----------------+

Sample table: customer1

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

To remove all rows from the table 'daysorder', the following SQL statement can be used :


-- Delete all records from the table named 'daysorder'
DELETE FROM daysorder;

Explanation:

This SQL statement deletes all records from the table named 'daysorder'.
DELETE FROM is the SQL command used to remove records from a table.
daysorder is the name of the table from which records will be deleted.
The semicolon ; denotes the end of the SQL statement.

Output:

SQL deleting records with where

In this page, we are going to discuss, how the WHERE clause along with SQL DELETE command can be used to remove number of rows against some conditions.

Example:

To remove rows from the table 'customer1' with the following condition -

1. 'cust_country' must be 'Canada',

the SQL statement can be used:

SQL Code


-- This SQL code deletes records from the 'customer1' table where the 'cust_country' column value is 'Canada'.
DELETE FROM customer1 -- This line specifies the action of deleting records from the 'customer1' table.
WHERE cust_country='Canada'; -- This line specifies the condition for deletion, selecting only rows where the 'cust_country' column has the value 'Canada'.

Explanation:

This SQL code performs a deletion operation on a database table named 'customer1'.
It removes records from the table where the country of the customer is specified as 'Canada'.
The operation is focused on one specific condition, which is the country of the customer being 'Canada'. Any record meeting this condition will be deleted.
The 'DELETE FROM' statement is used to remove records from the specified table.
The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, only records where the 'cust_country' column has the value 'Canada' will be deleted.

Output:

SQL Delete records using comparison operators

In this page, we are going to discuss, how the COMPARISON operator along with SQL DELETE command can be used to remove specific rows from a table.

Sample tables associated with this page have shown bellow:

Sample table: customer1

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

Sample table: daysorder

+---------+------------+----------------+------------+-----------+------------+-----------------+
| ORD_NUM | ORD_AMOUNT | ADVANCE_AMOUNT | ORD_DATE   | CUST_CODE | AGENT_CODE | ORD_DESCRIPTION |
+---------+------------+----------------+------------+-----------+------------+-----------------+
|  200107 |    4500.00 |         900.00 | 2008-08-30 | C00007    | A010       | SOD             |
|  200114 |    3500.00 |        1500.00 | 2008-08-15 | C00002    | A008       | SOD             |
|  200134 |    4200.00 |        1800.00 | 2008-09-25 | C00004    | A005       | SOD             |
|  200115 |    2000.00 |        1200.00 | 2008-02-08 | C00013    | A013       | SOD             |
|  200101 |    3000.00 |        1000.00 | 2008-07-15 | C00001    | A008       | SOD             |
|  200128 |    3500.00 |        1500.00 | 2008-07-20 | C00009    | A002       | SOD             |
|  200132 |    4000.00 |        1500.00 | 2008-08-15 | C00013    | A013       | SOD             |
+---------+------------+----------------+------------+-----------+------------+-----------------+

Example

To remove rows from the table 'customer1' with the following condition -

1. 'cust_country' must be 'India',

2. 'cus_city' must not be 'Chennai',

the following SQL statement can be used :

SQL Code:


-- This SQL code deletes records from the 'customer1' table where the 'cust_country' column value is 'India'
-- and the 'cust_city' column value is not 'Chennai'.
DELETE FROM customer1 -- This line specifies the action of deleting records from the 'customer1' table.
WHERE cust_country='India' -- This line specifies the first condition for deletion, selecting only rows where the 'cust_country' column has the value 'India'.
AND cust_city<>'Chennai'; -- This line specifies the second condition for deletion, selecting only rows where the 'cust_city' column value is not 'Chennai'.

Explanation:

This SQL code deletes records from the 'customer1' table based on two conditions.
It removes records where the country of the customer is specified as 'India' and the city is not 'Chennai'.
The operation is focused on two specific conditions, which are the country of the customer being 'India' and the city not being 'Chennai'. Any record meeting both conditions will be deleted.
The 'DELETE FROM' statement is used to remove records from the specified table.
The 'WHERE' clause is used to specify the conditions that determine which records will be deleted. In this case, records where the 'cust_country' column has the value 'India' and the 'cust_city' column value is not 'Chennai' will be deleted.

Output:

SQL delete records using IN operator

In this page, we are going to discuss, how the SQL IN operator along with SQL DELETE command can be used to remove specific rows from a table.

Example:

To remove rows from the table 'daysorder' with the following condition -

1. 'advance_amount' should be other than 1000 and 2000,

the following SQL statement can be used:

SQL Code:


-- This SQL code deletes records from the 'daysorder' table where the 'advance_amount' column value is not 1000 or 2000.
DELETE FROM daysorder -- This line specifies the action of deleting records from the 'daysorder' table.
WHERE advance_amount -- This line specifies the condition for deletion, selecting only rows where the 'advance_amount' column does not match the specified values.
NOT IN (1000,2000); -- This line specifies the values that the 'advance_amount' column should not have in order for the corresponding rows to be deleted.

Explanation:

This SQL code performs a deletion operation on a database table named 'daysorder'.
It removes records from the table where the 'advance_amount' column value is neither 1000 nor 2000.
The operation is focused on one specific condition, which is the 'advance_amount' column not being equal to either 1000 or 2000. Any record meeting this condition will be deleted.
The 'DELETE FROM' statement is used to remove records from the specified table.
The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, records where the 'advance_amount' column value is not in the list (1000, 2000) will be deleted.

Output:

SQL delete records using BETWEEN operator

In this page, we are going to discuss, how the SQL BETWEEN operator along with SQL DELETE command can be used to remove specific rows from a table.

Example:

To remove rows from the table 'daysorder' with the following condition -

1. 'advance_amount' should be within the range of 100 and 800,

the following SQL statement can be used:

SQL Code:


-- This SQL code deletes records from the 'daysorder' table where the 'advance_amount' column value is between 100 and 800.
DELETE FROM daysorder -- This line specifies the action of deleting records from the 'daysorder' table.
WHERE advance_amount -- This line specifies the condition for deletion, selecting only rows where the 'advance_amount' column value falls within a specified range.
BETWEEN 100 AND 800; -- This line specifies the range of values for the 'advance_amount' column, inclusive of both 100 and 800.

Explanation:

This SQL code performs a deletion operation on a database table named 'daysorder'.
It removes records from the table where the 'advance_amount' column value falls within the range of 100 to 800.
The operation is focused on one specific condition, which is the 'advance_amount' column value being between 100 and 800. Any record meeting this condition will be deleted.
The 'DELETE FROM' statement is used to remove records from the specified table.
The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, records where the 'advance_amount' column value is between 100 and 800 (inclusive) will be deleted.

Output:

SQL delete records using LIKE operator

In this page, we are going to discuss, how the SQL LIKE operator along with SQL DELETE command can be used to remove specific rows from a table.

Example:

To remove rows from the table 'customer1' with the following condition -

1. 'cust_city' should begin with the letter 'L',

the following SQL statement can be used:

SQL Code:


-- This SQL code deletes records from the 'customer1' table where the 'cust_city' column value starts with 'L'.
DELETE FROM customer1 -- This line specifies the action of deleting records from the 'customer1' table.
WHERE cust_city -- This line specifies the condition for deletion, selecting only rows where the 'cust_city' column value matches a specified pattern.
LIKE 'L%'; -- This line specifies the pattern that the 'cust_city' column values should match, where 'L%' means any string starting with 'L'.

Explanation:

This SQL code performs a deletion operation on a database table named 'customer1'.
It removes records from the table where the 'cust_city' column value starts with the letter 'L'.
The operation is focused on one specific condition, which is the 'cust_city' column value starting with 'L'. Any record meeting this condition will be deleted.
The 'DELETE FROM' statement is used to remove records from the specified table.
The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, records where the 'cust_city' column value starts with 'L' will be deleted, using the 'LIKE' operator for pattern matching.

Output:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: UPDATE using subqueries
Next: SQL Delete with subqueries