SQL DELETE: Removing Rows from a Table

Lesson 8.3 · Reading time: ~6 min · Practice tasks ↓

The SQL DELETE statement removes rows from a table. In this lesson you'll learn the DELETE ... WHERE syntax, how to delete rows by a condition on another table, why foreign keys sometimes block a deletion, how to check a DELETE before running it, and how it differs from TRUNCATE.

DELETE is the last of the three data-changing statements. It is the simplest to write and the most dangerous: one missing condition removes every row of a table. The rules of this lesson are about deleting exactly the rows you mean.

The sample databases on SQLtest are read-only, so these statements return a permission error there. The examples use the Sakila schema.

DELETE Syntax

DELETE FROM table_name
WHERE condition;

• DELETE FROM names the table.
• WHERE selects the rows to remove. Without WHERE, all rows are removed.
• Only rows are removed: the table, its columns and indexes stay.

Deleting Rows by a Condition

DELETE FROM payment
WHERE payment_date < '2005-05-25';

Result: all payments made before May 25, 2005 are removed.

Deleting by the primary key removes exactly one row:

DELETE FROM payment
WHERE payment_id = 16049;

Deleting by a Condition on Another Table

A subquery in WHERE selects rows by data from other tables. Delete the payments of inactive customers:

DELETE FROM payment
WHERE customer_id IN (
    SELECT customer_id
    FROM customer
    WHERE active = 0
);

Result: all payments of the 15 inactive customers are removed.

As with UPDATE, MySQL and MariaDB don't allow the subquery to read the table you delete from. MySQL also supports a multi-table form: DELETE p FROM payment AS p INNER JOIN customer AS c ON c.customer_id = p.customer_id WHERE c.active = 0;.

Foreign Keys Can Block a DELETE

Other tables reference customers: rental and payment store customer_id. In Sakila these foreign keys are defined with ON DELETE RESTRICT, so the database refuses to delete a customer who still has rentals or payments:

DELETE FROM customer
WHERE customer_id = 1;

Result: an error: "Cannot delete or update a parent row: a foreign key constraint fails". The customer stays.

This protects the data: without it, rentals would point to a customer who no longer exists. To delete such a customer, you first delete or reassign the dependent rows, or the foreign key is defined with ON DELETE CASCADE, which removes them automatically. You'll learn foreign key options together with CREATE TABLE in Lesson 9.1.

Check Before You Delete

1. Run a SELECT with the same WHERE and look at the rows.
2. Run the DELETE and compare the affected row count.

-- 1. Which rows will be removed?
SELECT payment_id, customer_id, amount, payment_date
FROM payment
WHERE payment_date < '2005-05-25';

-- 2. Remove exactly those rows
DELETE FROM payment
WHERE payment_date < '2005-05-25';

In production, run important deletions in a transaction and make sure there is a backup. Many systems don't delete business data at all: they mark rows as deleted (active = 0, deleted_at), which is called a soft delete.

DELETE Without WHERE and TRUNCATE

DELETE FROM table_name; removes all rows but keeps the table. TRUNCATE TABLE table_name; does the same much faster, but works differently:

You'll learn TRUNCATE and DROP TABLE in Lesson 9.3.

Key Takeaways:

• DELETE FROM table WHERE condition removes the matching rows; the table remains.
• Without WHERE, all rows are deleted.
• A subquery in WHERE deletes by conditions on other tables.
• Foreign keys with RESTRICT block deleting rows that are still referenced.
• Check with SELECT first; use transactions and backups for important deletions.
• To empty a whole table quickly, use TRUNCATE.

Frequently Asked Questions

What happens if I run DELETE without WHERE?

All rows of the table are removed; the empty table remains. Unless the statement ran in a transaction that you can roll back, the data can only be restored from a backup.

Why can't I delete a row in SQL?

Usually because of a foreign key: another table still references the row. Delete or change the dependent rows first, or define the foreign key with ON DELETE CASCADE.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one and can use WHERE; TRUNCATE removes all rows at once, is much faster, and resets the auto-increment counter.