SQL > SQL Commands > Delete From Statement

The DELETE FROM statement in SQL is used to remove records from a table.

Please note that the DELETE FROM command cannot delete any rows of data that would violate FOREIGN KEY or other constraints.

Syntax

The syntax for the DELETE FROM statement is as follows:

DELETE FROM "table_name"
WHERE "condition";

The WHERE clause is important here. Without specifying a condition, all records from the table will be deleted.

"Condition" can be simple (such as "Sales > 500") or complex (such as from the result of a subquery).

Examples

Two examples of how to use the DELETE FROM statement are shown below.

Example 1: DELETE FROM using a simple condition

We use the following table as the starting point.

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles 1500  Jan-05-1999 
 San Diego 250  Jan-07-1999 
 Los Angeles 300  Jan-08-1999 
 Boston 700  Jan-08-1999 

We decide not to keep any information on Los Angeles in this table. To accomplish this, we type the following SQL:

DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';

Now the table becomes,

Table Store_Information

 Store_Name  Sales  Txn_Date 
 San Diego 250  Jan-07-1999 
 Boston 700  Jan-08-1999 

Example 2: DELETE FROM using the results from a subquery

In Example 1, the criteria we use to determine which rows to delete is quite simple. We can also use a more complex condition. Below is an example where we use a subquery as the condition. Assume we have the following two tables:

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles 1500  Jan-05-1999 
 San Diego 250  Jan-07-1999 
 Los Angeles 300  Jan-08-1999 
 Boston 700  Jan-08-1999 

Table Geography

 Region_Name  Store_Name 
 East  Boston 
 East  New York 
 West  Los Angeles 
 West  San Diego 

We want to remove data for all stores in the East region from Store_Information (assuming that a store is either in the East region or the West region—it cannot be in more than one region). We use the following SQL statement to accomplish this:

DELETE FROM Store_Information
WHERE Store_Name IN
(SELECT Store_Name FROM Geography
WHERE Region_Name = 'East');

Upon execution, the Store_Information table becomes,

 Store_Name  Sales  Txn_Date 
 Los Angeles 1500  Jan-05-1999 
 San Diego 250  Jan-07-1999 
 Los Angeles 300  Jan-08-1999 

If we leave out the WHERE clause in a DELETE FROM command, we will delete all rows from the table. Most times, this is not what we intend to do. To prevent this, it is a best practice in database management to always run the corresponding SELECT statement first to make sure the rows selected are the ones we intend to remove from the table. This can be done by replacing "DELETE" with "SELECT *".

Exercises

For the questions below, we use the following table as the starting point:

Table Clients

 Customer_ID  Last_Name  First_Name  City  State  Join_Date 
 2  Larry  Kerr  Seattle  WA  Oct-15-2001 
 5  Aaron  Wallace  Denver  CO  Oct-18-2001 
 6  Jayson  Fortran  Raleigh  NC  Oct-24-2001 
 12  Jill  Dobbs  Buffalo  NY  Nov-15-2001 
 13  Lisa  Yamaguchi  San Diego  CA  Nov-15-2001 
 20  Ally  Smith  Seattle  WA  Nov-25-2001 
 67  Teyu  Lee  Cupertino  CA  Jan-11-2002 

1. Which of the following SQL statements is valid? (There may be more than one answer)
a) DELETE * FROM Clients WHERE State = 'CO';
b) DELETE FROM Clients WHERE State = 'CO';
c) DELETE FROM Clients HAVING State = 'CO';
d) DELETE FROM Clients WHERE Customer_ID < 10;

2. How many rows are deleted after the following SQL statement is executed?
DELETE FROM Clients WHERE State = 'CO';

3. What is the effect of the following SQL?
DELETE FROM Clients WHERE 1 = 1;

Next: SQL Comment

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact