The DELETE Statement: Removing Data

So far, you've learned how to retrieve data using SELECT, add new data with INSERT, and modify existing data with UPDATE. The final fundamental operation in managing data is removing it. For this task, SQL provides the DELETE statement.

The DELETE statement is used to remove one or more rows from a table. Unlike UPDATE which changes existing data, DELETE completely removes entire records.

Basic Syntax

The simplest form of the DELETE statement looks like this:

DELETE FROM table_name;

Warning: Executing DELETE without a WHERE clause, as shown above, will remove all rows from the specified table_name. This action is usually irreversible, so it should be used with extreme caution, if ever. Most database systems will require specific permissions to perform such a drastic operation, and often, you'll want to remove only specific rows.

Removing Specific Rows with WHERE

To remove only certain rows that meet specific criteria, you must include a WHERE clause. This is the most common and safest way to use the DELETE statement. The WHERE clause works exactly like it does with SELECT and UPDATE statements, allowing you to specify conditions that identify the rows to be removed.

The syntax is:

DELETE FROM table_name
WHERE condition;

Here, table_name is the table you want to remove rows from, and condition specifies which rows should be deleted. Only the rows for which the condition evaluates to true will be removed.

Examples

Let's use a sample Products table to illustrate how DELETE works.

Products Table:

ProductID	ProductName	Category	Price	StockQuantity
101	Eco Mug	Kitchenware	12.50	55
102	Wireless Mouse	Electronics	25.00	30
103	Notebook	Stationery	3.75	150
104	USB C Cable	Electronics	8.00	75
105	Desk Lamp	Home Goods	35.00	20
106	Stapler	Stationery	5.50	0

Example 1: Deleting a Single Row by ID

Suppose the 'Desk Lamp' (ProductID 105) has been discontinued. You can remove it using its unique ProductID:

DELETE FROM Products
WHERE ProductID = 105;

After executing this statement, the row with ProductID 105 will be permanently removed from the Products table.

Example 2: Deleting Multiple Rows Based on a Condition

Imagine you want to remove all products in the 'Stationery' category that are out of stock (StockQuantity is 0).

DELETE FROM Products
WHERE Category = 'Stationery' AND StockQuantity = 0;

This command would find rows where the Category is 'Stationery' and the StockQuantity is 0. In our sample data, this would delete the 'Stapler' (ProductID 106).

Proceed with Caution

The DELETE statement is powerful and permanent. Once rows are deleted, recovering them can be difficult or impossible without backups.

A Recommended Safety Step:

Before executing a DELETE statement with a WHERE clause, it's often a good practice to first run a SELECT statement using the exact same WHERE clause. This allows you to preview which rows will be affected by your DELETE command.

For instance, before running the command to delete out-of-stock stationery:

-- First, preview the rows to be deleted
SELECT *
FROM Products
WHERE Category = 'Stationery' AND StockQuantity = 0;

-- If the result is what you expect, then execute the DELETE
-- DELETE FROM Products
-- WHERE Category = 'Stationery' AND StockQuantity = 0;

This preview step helps prevent accidental deletion of the wrong data. Always double-check your WHERE clause before executing a DELETE command, especially in a production environment.

With DELETE, you now have the fundamental tools (SELECT, INSERT, UPDATE, DELETE) to perform the most common data manipulation tasks in a relational database using SQL. The next section provides opportunities to practice these commands.