Delete Statement

 

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.

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:

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:

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:

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.

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	google.com
5000	Smith	Jane	microsoft.com
6000	Ferguson	Samantha	google.com
7000	Reynolds	Allen	youtube.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	facebook.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 Table A whose data in field1 & field2 DO NOT match the data in fields & fields of Table B?

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);

 

sql-update

 

SQL: UPDATE Statement

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

Description

The SQL UPDATE statement is used to update existing records in the tables.

Syntax

The syntax for the UPDATE statement when updating a table in SQL is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

OR

The syntax for the SQL UPDATE statement when updating a table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

OR

The syntax for the SQL UPDATE statement when updating multiple tables (not permitted in Oracle) is:

UPDATE table1, table2, ...
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
[AND conditions];

Parameters or Arguments

column1, column2

The columns that you wish to update.

expression1, expression2

These are the new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

WHERE conditions

Optional. The conditions that must be met for the update to execute. If no conditions are provided, then all records in the table will be updated.

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 - Update single column

Let's look at an example showing how to use the SQL UPDATE statement to update a single column in a table.

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

cust_id	last_name	first_name	favorite_website
4000	Jackson	Joe	microsoft.com
5000	Smith	Jane	google.com
6000	Ferguson	Samantha	blogger.com
7000	Reynolds	Allen	youtube.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	facebook.com

Now let's demonstrate how the UPDATE statement works by updating one column in the customers table. Enter the following UPDATE statement:

UPDATE customers
SET first_name = 'Judy'
WHERE cust_id = 8000;

There will be 1 record updated. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	microsoft.com
5000	Smith	Jane	google.com
6000	Ferguson	Samantha	facebook.com
7000	Reynolds	Allen	youtube.com
8000	Anderson	Judy	NULL
9000	Johnson	Derek	google.com

In this UPDATE example, the first_name field is set to 'Judy' in the customers table where the cust_id is equal to 8000.

Example - Update multiple columns

Let's look at an UPDATE example that shows how to update more than one column in a table.

TIP: When you update multiple columns in an UPDATE statement, you need to comma separate the column/value pairs in the SET clause.

In this UPDATE 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

Now let's demonstrate how to use the UPDATE statement to update more than one column value at once. Enter the following UPDATE statement:

UPDATE suppliers
SET supplier_id = 150,
    supplier_name = 'Apple',
    city = 'Cupertino'
WHERE supplier_name = 'Google';

There will be 1 record updated. 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
100	Microsoft	Redmond	Washington
150	Apple	Cupertino	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 UPDATE example would update the supplier_id to 150, the supplier_name to 'Apple' and city to 'Cupertino' where the supplier_name is 'Google'.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table.

In this UPDATE 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

And a table called summary_data with the following data:

product_id	current_category
1	10
2	10
3	10
4	10
5	10
8	10

Now let's update the summary_data table with values from the products table. Enter the following UPDATE statement:

UPDATE summary_data
SET current_category = (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id);

There will be 5 records update. Select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

product_id	current_category
1	50
2	50
3	50
4	50
5	75
8	10

This example would update the current_category field in the summary_data table with the category_id from the products table where the product_id values match. The first 5 records in the summary_data table have been updated.

TIP: Notice that our UPDATE statement included an EXISTS condition in the WHERE clause to make sure that there was a matching product_id in both the products and summary_data table before updating the record.

If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_category field to NULL in the 6th row of the summary_data table (because the products table does not have a record where product_id=8).

Practice Exercises

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

These exercises allow you to try out your skills with the UPDATE 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!