SQL DELETE

SQL > SQL Commands > Delete From Statement

Sometimes we may wish to use a query to remove records from a table. To do so, we can use the DELETE FROM command. The syntax for this is

DELETE FROM "table_name"
WHERE "condition";

It is easiest to use an example. We start with the table below:

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

and 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

In this example, the criteria we use to determine which rows to delete is 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

and we want to remove data for all stores in the East region from Store_Information. We would 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 *".

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