totn SQL

SQL: DELETE Statement

This SQL tutorial explains how to use the SQL DELETE statement with syntax, examples, and practice exercises.

Description

The SQL DELETE statement is a used to delete one or more records from a table.

subscribe button Subscribe

Syntax

The syntax for the DELETE statement in SQL is:

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table
The table that you wish to delete records from.
WHERE conditions
Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, all records in the table will be deleted.

Note

  • You do not need to list fields in the DELETE statement since you are deleting the entire row from the table.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - DELETE Statement with One Condition

If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.

Let's start with a simple example of a DELETE query that has one condition in the WHERE clause.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Enter the following DELETE statement:

Try It
DELETE FROM suppliers
WHERE supplier_name = 'Microsoft';

There will be 1 record deleted. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

This example would delete all records from the suppliers table where the supplier_name is 'Microsoft'.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete:

Try It
SELECT COUNT(*)
FROM suppliers
WHERE supplier_name = 'Microsoft';

This quey will return number of records that will be deleted when you execute the DELETE statement.

COUNT(*)
1

Example - DELETE Statement with more than One Condition

You can have more than one condition in a DELETE statement in SQL using either the AND condition or the OR condition. The AND condition allows you to delete a record if all of the conditions are met. The OR condition deletes a record if any one of the conditions are met.

Let's look at an example of how to use the DELETE statement with two conditions using the AND condition.

In this example, we have a table called products with the following data:

product_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

Enter the following DELETE statement:

Try It
DELETE FROM products
WHERE category_id = 50
AND product_name <> 'Pear';

There will be 3 records deleted. Select the data from the products table again:

SELECT * FROM products;

These are the results that you should see:

product_id product_name category_id
1 Pear 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

This example would delete all records from the products table whose category_id is 50 and whose product_name is not Pear.

To check for the number of rows that will be deleted, you can run the following SELECT statement before performing the delete.

Try It
SELECT COUNT(*)
FROM products
WHERE category_id = 50
AND product_name <> 'Pear';

This will return number of records that will be deleted when you execute the DELETE statement.

COUNT(*)
3

Example - Using EXISTS with the DELETE Statement

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And a table called orders with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following DELETE statement:

DELETE FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

There will be 1 record deleted. Select the data from the orders table again:

SELECT * FROM orders;

These are the results that you should see:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
5 NULL 2016/05/01

This example would delete all records from the orders table where there is a record in the customers table with the last_name of 'Jackson' and a matching customer_id value in both tables. In this example, the record for order_id=4 was deleted.

If you want to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

SELECT COUNT(*) FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

This will return number of records that will be deleted when you execute the DELETE statement.

COUNT(*)
1

Frequently Asked Questions

Question: How would I write a SQL DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this for your SQL DELETE statement:

DELETE FROM TableA
WHERE NOT EXISTS
  (SELECT *
   FROM TableB
   WHERE TableA.field1 = TableB.fieldx
   AND TableA.field2 = TableB.fieldz);

Practice Exercises

If you want to test your skills using the SQL DELETE statement, try some of our practice exercises.

These exercises allow you to try out your skills with the DELETE statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises