The DELETE FROM statement in SQL is used to remove records from a table.
Note:
The DELETE FROM command cannot delete any rows of data that would violet FOREIGN KEY or other Constraints.
Syntax:
DELETE FROM “table_name” WHERE “condition”;
The WHERE clause is very important here. Without specifying a condition, all records from the table will be deleted.
Examples:
The following table is used to perform DELETE operation
Table Store_Information
Example1: DELETE FROM using a simple condition
In this example keyboards are shipped so decided to not keep information regarding keyboards in this table. To achieve this we need to use following SQL query
DELETE FROM Store_Information
WHERE Store_Name = ‘Los Angeles’;
Now the table becomes
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
Table Geography
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,
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 *”.