Basic about DELETE Statement – using and example

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

Description

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

Subscribe

Syntax

The syntax for the DELETE statement in SQL is:

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table The desk that you desire to delete archives from. WHERE conditions Optional. The conditions that ought to be met for the documents to be deleted. If no prerequisites are provided, all data in the table will be deleted.

Note

You do not need to listing fields in the DELETE statement given that you are deleting the complete row from the table.

DDL/DML for Examples

If you want to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your very 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 documents from the desk will be deleted. As a result, you will most often consist of a WHERE clause with at least one circumstance in your DELETE statement.

Let’s start with a simple instance of a DELETE query that has one circumstance in the WHERE clause.

In this example, we have a desk referred to as suppliers with the following data:

supplier_id supplier_name town 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 file deleted. Select the records from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name town 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 archives from the suppliers desk where the supplier_name is ‘Microsoft’.

You may additionally wish to check for the variety of rows that will be deleted. You can decide the quantity of rows that will be deleted via walking the following SELECT statement before performing the delete:

Try It

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

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

COUNT(*)

1

Example – DELETE Statement with greater than One Condition

You can have more than one situation in a DELETE assertion in SQL the usage of both the AND situation or the OR condition. The AND situation lets in you to delete a document if all of the prerequisites are met. The OR situation deletes a file if any one of the prerequisites are met.

Let’s seem to be at an instance of how to use the DELETE assertion with two prerequisites the usage of the AND condition.

In this example, we have a desk known as merchandise 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 three files deleted. Select the information from the products desk 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 instance would delete all archives from the products table whose category_id is 50 and whose product_name is not Pear.

To test for the wide variety of rows that will be deleted, you can run the following SELECT declaration earlier than performing the delete.

Try It

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

This will return quantity of documents 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 additionally desire to delete records in one table based totally on values in every other table. Since you cannot listing 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 known as clients 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 desk 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 statistics 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 archives from the orders desk the place there is a file in the customers table with the last_name of ‘Jackson’ and a matching customer_id price in each tables. In this example, the document for order_id=4 was once deleted.

If you want to determine the variety of rows that will be deleted, you can run the following SQL SELECT declaration earlier than 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 range of data that will be deleted when you execute the DELETE statement.

COUNT(*)

1

Frequently Asked Questions

Question: How would I write a SQL DELETE assertion to delete all files in TableA whose data in field1 & field2 DO NOT healthy the statistics in fieldx & fieldz of TableB?

Answer: You ought to strive 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 prefer to take a look at your capabilities the usage of the SQL DELETE statement, try some of our exercise exercises.

These workout routines permit you to strive out your capabilities with the DELETE statement. You will be given questions that you want to solve. After each exercise, we grant the solution so you can take a look at your answer. Give it a try!

Go to Practice Exercises