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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-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_NameSalesTxn_Date
San Diego250Jan-07-1999
Boston700Jan-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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

Table Geography

Region_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan 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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-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.

Next: SQL Functions




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy