SQL DELETE Statement: Remove Rows from a Table (Examples)

SQL Tutorial · Chapter 27 of 32

The SQL DELETE statement removes rows from a table. With a WHERE clause it deletes only the rows that match your condition; without one it empties the whole table. DELETE removes data but keeps the table itself, which is what separates it from TRUNCATE (fast removal of every row) and DROP (removal of the table).

This guide is for beginners and for analysts who write the occasional maintenance query. You will see the syntax, a sample Orders table with before-and-after results for every example, deletes that use a subquery or a join, how to make a delete reversible with a transaction, and the mistakes that cause most data loss. Every example works in MySQL, SQL Server, PostgreSQL and SQLite unless a note says otherwise.

SQL DELETE Statement Syntax

DELETE FROM table_name
WHERE condition;

-- remove every row but keep the table
DELETE FROM table_name;

DELETE has no column list, because it always removes whole rows. If you only want to blank out one column, use the SQL UPDATE statement and set that column to NULL instead.

Sample Tables Used in This Guide

All examples start from these two small tables. Orders holds six orders; Customers holds five customers. Notice that order 105 points to customer 6, who does not exist in Customers.

Orders (before any delete)

Customers

Example 1: Delete One Row by Primary Key

DELETE FROM Orders
WHERE OrderID = 102;

SELECT OrderID, Product FROM Orders ORDER BY OrderID;

Five rows remain. Filtering on the primary key is the safest form of DELETE because it can never touch more than one row.

Example 2: Delete Several Rows with a Condition

Continuing from Example 1, remove every order worth less than 100.

DELETE FROM Orders
WHERE Amount < 100;

SELECT OrderID, Product, Amount FROM Orders ORDER BY OrderID;

Orders 104 (45) and 105 (80) are gone, along with 102 from the previous step. Any condition that works in a SQL WHERE clause works here too: IN, BETWEEN, LIKE, AND/OR and IS NULL.

Example 3: Delete Orphan Rows with a Subquery

Starting again from the original six rows, remove orders whose customer does not exist in Customers.

DELETE FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers);

SELECT OrderID, CustomerID FROM Orders ORDER BY OrderID;

Only order 105 (customer 6) is removed. If the subquery column can contain NULL, NOT IN returns no rows at all, so prefer NOT EXISTS:

DELETE FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID = o.CustomerID);

This alias form works in PostgreSQL. In SQL Server and older MySQL versions write DELETE o FROM Orders o WHERE NOT EXISTS (...); in SQLite drop the alias and refer to Orders.CustomerID inside the subquery. For more on nested queries, see SQL subqueries explained.

Example 4: Delete Inside a Transaction

A transaction lets you check the result and undo it if the row count looks wrong. Starting from the original six rows:

BEGIN;                       -- BEGIN TRANSACTION in SQL Server
DELETE FROM Orders WHERE OrderDate < '2026-02-01';
SELECT COUNT(*) FROM Orders; -- expect 3
ROLLBACK;                    -- or COMMIT to keep the change

Orders 101, 102 and 103 were placed in January, so three rows remain inside the transaction. After ROLLBACK all six rows are back. Most tools run in autocommit mode by default, so start the transaction explicitly before you run the DELETE.

Example 5: Delete Using a Join

To delete every order that belongs to a UK customer, you can match the two tables. The basic DELETE is the same everywhere, but the join form differs by database:

-- MySQL
DELETE o FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE c.Country = 'UK';

-- SQL Server
DELETE o FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE c.Country = 'UK';

-- PostgreSQL
DELETE FROM Orders o
USING Customers c
WHERE c.CustomerID = o.CustomerID
  AND c.Country = 'UK';

On the original data, each version removes order 102 (customer 2, Tom Hughes). SQLite has no join form, so use a subquery: WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'UK'). If joins are new to you, start with SQL INNER JOIN.

Example 6: See Which Rows Were Deleted

PostgreSQL (and SQLite 3.35 or later) can return the removed rows, and SQL Server has OUTPUT. This is useful for logging or for a quick sanity check.

-- PostgreSQL / SQLite
DELETE FROM Orders WHERE Amount < 100
RETURNING OrderID, Amount;

-- SQL Server
DELETE FROM Orders
OUTPUT deleted.OrderID, deleted.Amount
WHERE Amount < 100;

On the original data both return three rows: 102 (25), 104 (45) and 105 (80).

A Safe DELETE Workflow

1. Write the SELECT first. Run SELECT * FROM Orders WHERE Amount < 100; and check that it returns exactly the rows you expect.
2. Keep the same WHERE clause. Change only SELECT * to DELETE. Do not retype the condition.
3. Wrap it in a transaction. Run BEGIN, the DELETE, then a quick COUNT(*).
4. Compare the row count. The “rows affected” number should match the count from step 1. If it does not, run ROLLBACK.
5. Commit and back up. Run COMMIT only when you are sure, and take a backup first on production data.

DELETE vs TRUNCATE vs DROP

Database Differences to Know

• A basic DELETE ... WHERE is identical in MySQL, SQL Server, PostgreSQL and SQLite.
• Limiting rows: MySQL allows DELETE FROM Orders WHERE Amount < 100 ORDER BY OrderDate LIMIT 2;. SQL Server uses DELETE TOP (2) FROM Orders WHERE .... PostgreSQL has no LIMIT on DELETE, so you filter the keys in a subquery instead.
• TRUNCATE exists in MySQL, SQL Server and PostgreSQL. SQLite has no TRUNCATE; DELETE FROM table with no WHERE is optimised internally.
• Foreign keys: with ON DELETE CASCADE, deleting a parent row also removes its child rows. Without it, deleting a customer who still has orders fails with a foreign key error.
• MySQL Workbench turns on “safe updates” by default, which blocks DELETE without a key column in WHERE. That setting is there to protect you.

Soft Deletes: When Not to Use DELETE

Many applications never run DELETE on business data. Instead they add an IsDeleted flag or a DeletedAt timestamp, set it with UPDATE, and filter those rows out of normal queries. This keeps history for audits and makes accidental removals reversible.

UPDATE Orders SET DeletedAt = CURRENT_TIMESTAMP WHERE OrderID = 102;

SELECT * FROM Orders WHERE DeletedAt IS NULL;

Use a real DELETE for temporary, staging or log data with no long-term value, or when a privacy request requires the data to be removed.

Common Mistakes and Fixes

• DELETE without WHERE. The table is emptied. Fix: always write and test the SELECT first, and use a transaction.
• DELETE * FROM Orders. This is a syntax error almost everywhere because DELETE takes no column list (MS Access is the exception). Fix: DELETE FROM Orders.
• Foreign key error when deleting a parent row. Child rows still reference it. Fix: delete the children first, or define ON DELETE CASCADE on purpose.
• NOT IN returns nothing. The subquery contains a NULL. Fix: use NOT EXISTS.
• Very slow delete on a huge table. Fix: use TRUNCATE when you need every row gone, or delete in batches (for example 10,000 rows at a time) so locks and the transaction log stay small.

Practice Exercise

Use the original six-row Orders table and the Customers table above.

1. Delete all orders placed in February 2026.
2. Delete every order that belongs to a customer from the UK, using a subquery.

Answers

-- 1: removes orders 104 and 105
DELETE FROM Orders
WHERE OrderDate >= '2026-02-01' AND OrderDate < '2026-03-01';

-- 2: removes order 102
DELETE FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'UK');

Using >= and < for date ranges is safer than BETWEEN, because it still works when the column also stores a time of day. If you want to put rows back after practising, see SQL INSERT INTO. New to SQL? Start with the SQL introduction.

Prefer to build data-handling skills in a spreadsheet as well? The Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy takes you through filtering and cleaning data with practice files.

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one at a time, can use WHERE, fires triggers and can be rolled back inside a transaction. TRUNCATE removes every row at once, cannot filter, is much faster and usually resets auto-increment counters.

Can I recover rows after a DELETE?

Only if the DELETE ran inside a transaction that you then rolled back, or if you restore from a backup or a point-in-time recovery. A committed delete is permanent.

Does DELETE remove the table?

No. The table, its columns, indexes and constraints all stay; only the data goes. DROP TABLE removes the table itself.

How do I delete duplicate rows in SQL?

Keep one row per group and delete the rest. A common pattern is DELETE FROM Orders WHERE OrderID NOT IN (SELECT MIN(OrderID) FROM Orders GROUP BY CustomerID, Product, OrderDate);. MySQL does not allow selecting from the same table in that subquery, so wrap it in a derived table there.

Why does my DELETE say 0 rows affected?

No row matched the WHERE condition. Check data types (a text column compared with a number), trailing spaces, date formats and NULLs. Running the same condition as a SELECT shows quickly what is matching.

Can I delete from two tables in one statement?

MySQL allows DELETE o, c FROM Orders o JOIN Customers c .... Other databases delete from one table per statement, or rely on ON DELETE CASCADE to remove child rows automatically.

Wrapping Up

DELETE is simple to write and easy to get wrong. Test the WHERE clause as a SELECT, run the delete inside a transaction, and check the row count before you commit. Use TRUNCATE when you want every row gone, and consider a soft delete when the business needs to keep history. Next, practise the related change statement, SQL UPDATE, which uses the same WHERE logic to modify rows instead of removing them.

📅 Last updated: October 2026

Chapter 27 of 32 · SQL from Scratch: all 32 chapters