The SQL DELETE statement removes rows from a table. You name the table, and a WHERE clause picks which rows go:
DELETE FROM table_name
WHERE condition;
Every row that matches the condition is deleted. Leave out WHERE and every row in the table is deleted. The table itself (its columns, indexes and permissions) stays; only the data goes. To remove the table too, you want DROP TABLE, covered below.
Jump to: sample data · delete one row · delete multiple rows · preview first · delete all rows · DELETE vs TRUNCATE vs DROP · undo with ROLLBACK · see what was deleted · DELETE with a subquery · DELETE with JOIN · delete duplicates · delete in batches · foreign keys and CASCADE · the NULL traps · soft delete · MySQL error 1175 · database differences · performance · mistakes · FAQ
Sample data
Every query below was run on SQLite 3.45 and PostgreSQL 16, and the output is copied from the real result. Each example starts from this fresh data, so the deletes don't pile up on each other. Where the two databases differ, it's called out.
CREATE TABLE customers (
id INTEGER PRIMARY KEY, name TEXT, email TEXT, status TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
amount INTEGER, status TEXT, ordered_at TIMESTAMP, coupon TEXT
);
INSERT INTO customers VALUES
(1, 'Alice', '[email protected]', 'active'),
(2, 'Bob', '[email protected]', 'active'),
(3, 'Carla', '[email protected]', 'closed'),
(4, 'Dan', '[email protected]', 'closed'),
(5, 'Erin', NULL, 'active');
INSERT INTO orders VALUES
(101, 1, 25, 'paid', '2025-11-03 10:00:00', NULL),
(102, 1, 80, 'cancelled', '2025-12-20 09:30:00', 'XMAS'),
(103, 2, 40, 'paid', '2026-01-15 14:30:00', NULL),
(104, 3, 120, 'cancelled', '2026-02-01 08:00:00', NULL),
(105, 3, 60, 'paid', '2026-03-10 16:45:00', 'SPRING'),
(106, 5, 15, 'pending', '2026-03-11 12:00:00', NULL),
(107, NULL, 30, 'paid', '2026-03-12 09:00:00', NULL);
Things to notice for later: orders.customer_id is a foreign key to customers, order 107 has no customer (a guest checkout, NULL), Erin has no email, and Dan has no orders at all.
Delete one row
To delete a single row, filter on something unique, almost always the primary key:
DELETE FROM orders
WHERE id = 104;
DELETE 1
PostgreSQL reports how many rows were removed (DELETE 1); MySQL says 1 row affected, SQL Server (1 row affected), and in SQLite you can check with SELECT changes();. Order 104 is gone and the other six are untouched:
SELECT id, customer_id, amount, status FROM orders;
id | customer_id | amount | status
----+-------------+--------+----------
101 | 1 | 25 | paid
102 | 1 | 80 | cancelled
103 | 2 | 40 | paid
105 | 3 | 60 | paid
106 | 5 | 15 | pending
107 | | 30 | paid
(6 rows)
Filtering on a name (WHERE customer = 'Carla') also works, but it deletes every row with that name. If you mean one specific row, use its id.
Delete multiple rows
DELETE takes the same WHERE clause as SELECT, so anything you can filter, you can delete. A condition that matches several rows deletes all of them in one statement.
By a column value
DELETE FROM orders
WHERE status = 'cancelled';
DELETE 2
Orders 102 and 104 were cancelled, so both are removed.
A list of ids with IN
DELETE FROM orders
WHERE id IN (101, 103);
DELETE 2
A pattern with LIKE
DELETE FROM orders
WHERE coupon LIKE 'X%';
DELETE 1
Only order 102 has a coupon starting with "X" (XMAS). Be careful with patterns in a delete: LIKE '%X%' would also match any code that merely contains an X, and whether 'x%' matches "XMAS" depends on the database. The SQL LIKE guide covers the wildcards and the case-sensitivity differences, which matter a lot more when the result is a delete.
A range with BETWEEN
DELETE FROM orders
WHERE amount BETWEEN 50 AND 100;
DELETE 2
Orders 102 (80) and 105 (60) go. BETWEEN includes both ends, so an order of exactly 50 or 100 would go too. See the SQL BETWEEN guide.
Older than a date
DELETE FROM orders
WHERE ordered_at < '2026-01-01';
DELETE 2
Everything before 2026 (orders 101 and 102) is removed. Use < on the first day you want to keep rather than BETWEEN or <= on a last day: with a timestamp column, <= '2025-12-31' stops at midnight and leaves the rest of December 31 behind. To delete "older than 90 days", compute the cutoff: ordered_at < CURRENT_DATE - INTERVAL '90 days' in PostgreSQL, < NOW() - INTERVAL 90 DAY in MySQL, < DATEADD(day, -90, GETDATE()) in SQL Server, < date('now', '-90 days') in SQLite.
Several conditions
Combine conditions with AND and OR exactly as in a query, and use parentheses whenever you mix them. WHERE status = 'paid' OR status = 'pending' AND amount < 20 means paid OR (pending AND under 20), because AND binds tighter. In a SELECT that's a wrong report; in a DELETE it's every paid order gone.
Always preview with SELECT first
The single best habit with DELETE: write it as a SELECT first, look at the rows, then swap the first line.
-- 1. Look
SELECT id, amount, ordered_at
FROM orders
WHERE ordered_at < '2026-01-01';
id | amount | ordered_at
----+--------+--------------------
101 | 25 | 2025-11-03 10:00:00
102 | 80 | 2025-12-20 09:30:00
(2 rows)
-- 2. Delete the same rows
DELETE FROM orders
WHERE ordered_at < '2026-01-01';
DELETE 2
The row count from the delete should match the preview. If the preview says 2 rows and the delete says 2,000, something changed in between, and that's what a transaction is for. Keeping the WHERE clause on its own line makes the swap from SELECT ... to DELETE a one-line edit with no chance to lose the filter.
Delete all rows from a table
A DELETE without WHERE deletes every row:
DELETE FROM orders;
SELECT COUNT(*) AS remaining FROM orders;
DELETE 7
remaining
---------
0
The table is still there, with its columns and indexes, just empty. That's occasionally what you want (clearing a staging table before a reload). Much more often it's an accident: someone highlighted only the first line of the statement in their SQL editor and ran it. For emptying a big table on purpose, TRUNCATE is usually faster.
DELETE vs TRUNCATE vs DROP
All three "remove data", at very different levels:
DELETE | TRUNCATE TABLE | DROP TABLE | |
|---|---|---|---|
| What it removes | Rows matching WHERE (or all rows) | All rows | The whole table: rows, columns, indexes, permissions |
Can filter with WHERE | Yes | No | No |
| Speed on a big table | Slow: removes and logs each row | Fast: deallocates the data pages | Fast |
Fires DELETE triggers | Yes | No | No |
| Resets auto-increment / identity | No | Yes in MySQL and SQL Server; PostgreSQL only with RESTART IDENTITY | Table is gone |
| Can be rolled back | Yes, inside a transaction | PostgreSQL and SQL Server: yes. MySQL and Oracle: no (it commits) | PostgreSQL and SQL Server: yes. MySQL and Oracle: no |
| Table referenced by a foreign key | Allowed if no child rows point at the deleted rows | Usually refused (PostgreSQL needs CASCADE) | Refused unless you drop the constraint or use CASCADE |
| SQLite | Yes | No TRUNCATE; DELETE FROM t; with no WHERE is optimised to do the same job | Yes |
Is TRUNCATE better than DELETE? For emptying a whole large table, yes: it's much faster and lighter on the transaction log. For anything else, no: it can't take a WHERE, it skips triggers, and in MySQL and Oracle you can't roll it back. There's a longer comparison in TRUNCATE TABLE vs DELETE vs DROP TABLE.
Undo a DELETE with a transaction
A committed DELETE can't be undone; the only way back is a backup. But a delete inside a transaction can be checked and thrown away before it becomes permanent:
BEGIN;
DELETE FROM orders WHERE status = 'paid';
SELECT COUNT(*) AS remaining FROM orders;
DELETE 4
remaining
---------
3
Four rows gone when you expected one? Throw it away:
ROLLBACK;
SELECT COUNT(*) AS remaining FROM orders;
remaining
---------
7
All seven orders are back. If the count looks right, run COMMIT; instead. SQL Server spells it BEGIN TRANSACTION (or BEGIN TRAN); MySQL accepts BEGIN or START TRANSACTION.
Two things to know. First, many tools run in autocommit mode, where every statement commits the moment it finishes, so you must type BEGIN yourself before the delete. Second, keep the transaction short: while it's open, the deleted rows stay locked and other sessions trying to change them will wait.
See which rows were deleted (RETURNING and OUTPUT)
PostgreSQL, SQLite (3.35 and later) and MariaDB can hand back the rows a delete removed, which is handy for logging or for moving rows to an archive:
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, amount;
id | amount
----+-------
102 | 80
104 | 120
(2 rows)
SQL Server uses an OUTPUT clause that reads from the virtual deleted table, placed before WHERE:
DELETE FROM orders
OUTPUT deleted.id, deleted.amount
WHERE status = 'cancelled';
MySQL has no equivalent; run a SELECT with the same WHERE inside the transaction first. In PostgreSQL you can archive and delete in one statement by putting the delete in a CTE:
WITH gone AS (
DELETE FROM orders WHERE status = 'cancelled' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM gone;
DELETE with a subquery or EXISTS
Often the rows to delete are defined by another table. "Delete every order from a closed customer" needs the customers table to know who is closed. The portable way, which works in every database, is a subquery in the WHERE clause:
DELETE FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE status = 'closed'
);
DELETE 2
Carla is the only closed customer with orders, so 104 and 105 are deleted. EXISTS does the same with a correlated subquery:
DELETE FROM orders AS o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id
AND c.status = 'closed'
);
DELETE 2
SQLite only accepts an alias on the target table when you write AS (DELETE FROM orders o is a syntax error there); PostgreSQL accepts both. To delete rows with no match in another table, use NOT EXISTS, not NOT IN; the NULL section shows why.
DELETE with JOIN
Standard SQL has no join in DELETE, so every database invented its own syntax. All four statements below delete the same rows as the subquery above: orders belonging to closed customers.
MySQL and MariaDB
List the table(s) to delete from between DELETE and FROM, then join as usual:
DELETE o
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.status = 'closed';
MySQL can delete from both tables at once with DELETE o, c FROM .... It also refuses a subquery that reads the table being deleted from (error 1093, "You can't specify target table for update in FROM clause"), which is one reason the join form is common in MySQL.
SQL Server
Same idea, with the alias after DELETE:
DELETE o
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.status = 'closed';
PostgreSQL
PostgreSQL uses USING for the other tables and puts the join condition in WHERE:
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
AND c.status = 'closed';
DELETE 2
SQLite and Oracle
Neither supports a join in DELETE (SQLite gives near "o": syntax error on the PostgreSQL form). Use the IN or EXISTS subquery from the previous section, which works everywhere and is what most people should reach for first.
To delete rows that have no partner, the join version is a LEFT JOIN ... WHERE IS NULL anti-join; in a DELETE, NOT EXISTS is clearer and portable.
Delete duplicate rows
Deleting duplicates means "delete every row except one per group", which needs a way to pick the keeper: usually the lowest id, or ROW_NUMBER() when the rule is fancier. The short version, which works in SQLite, PostgreSQL and SQL Server:
DELETE FROM contacts
WHERE id NOT IN (
SELECT MIN(id) FROM contacts GROUP BY email
);
Five methods, the MySQL workaround and how to stop duplicates coming back are in how to remove duplicates in SQL.
Delete in batches (LIMIT and TOP)
Deleting millions of rows in one statement holds locks for a long time and can balloon the transaction log. The fix is to delete a few thousand rows at a time and repeat until the count is zero.
-- MySQL (and SQLite builds compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
DELETE FROM orders WHERE status = 'paid' ORDER BY ordered_at LIMIT 5000;
-- SQL Server
DELETE TOP (5000) FROM orders WHERE status = 'paid';
PostgreSQL has no LIMIT on DELETE (you get syntax error at or near "ORDER"). Pick the ids in a subquery instead (this works in PostgreSQL and SQLite; MySQL and SQL Server use the forms above):
DELETE FROM orders
WHERE id IN (
SELECT id FROM orders
WHERE status = 'paid'
ORDER BY ordered_at
LIMIT 2
);
DELETE 2
The two oldest paid orders (101 and 103) are gone; 105 and 107 remain for the next batch. MySQL rejects LIMIT inside an IN subquery, which is why it has its own DELETE ... LIMIT. SQLite's DELETE ... LIMIT depends on how it was compiled: the sqlite3 command-line tool on Ubuntu has it, but don't count on it in every app.
Foreign keys, ON DELETE CASCADE and SET NULL
A foreign key stops you deleting a row that other rows still point at. Alice has orders, so:
DELETE FROM customers WHERE id = 1;
PostgreSQL:
ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL: Key (id)=(1) is still referenced from table "orders".
SQLite:
Runtime error near line 1: FOREIGN KEY constraint failed (19)
MySQL reports ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails, and SQL Server says The DELETE statement conflicted with the REFERENCE constraint. Nothing is deleted. You have three options: delete the child rows first (the orders, then the customer), change the constraint, or don't delete at all (see soft delete).
SQLite gotcha: SQLite only enforces foreign keys when PRAGMA foreign_keys = ON; has been run on the connection. It's off by default, so the delete above silently succeeds and leaves Alice's orders pointing at nobody.
ON DELETE CASCADE and ON DELETE SET NULL
The constraint itself can say what should happen to child rows. ON DELETE CASCADE deletes them along with the parent; ON DELETE SET NULL keeps them but clears the reference:
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
id INTEGER PRIMARY KEY, title TEXT,
author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);
CREATE TABLE reviews (
id INTEGER PRIMARY KEY, stars INTEGER,
book_id INTEGER REFERENCES books(id) ON DELETE SET NULL
);
INSERT INTO authors VALUES (1, 'Ann'), (2, 'Ben');
INSERT INTO books VALUES (10, 'SQL Basics', 1), (11, 'Joins', 1), (12, 'Indexes', 2);
INSERT INTO reviews VALUES (100, 5, 10), (101, 4, 12);
DELETE FROM authors WHERE id = 1;
DELETE 1
The delete reports one row, but three tables changed. Ann's two books were cascaded away, and the review of "SQL Basics" lost its book:
SELECT * FROM books;
id | title | author_id
---+---------+----------
12 | Indexes | 2
(1 row)
SELECT * FROM reviews ORDER BY id;
id | stars | book_id
----+-------+--------
100 | 5 |
101 | 4 | 12
(2 rows)
Cascades are convenient for data that truly belongs to its parent (order lines to an order). They're dangerous on anything else, because a one-row delete can quietly remove thousands of rows in other tables. Check a table's constraints before deleting from it; a GUI like Beekeeper Studio shows them in the table's structure view.
The NULL traps
WHERE column = NULL deletes nothing
Try to delete orders with no coupon:
DELETE FROM orders WHERE coupon = NULL;
DELETE 0
No error, no rows. Anything compared with NULL using = is unknown, never true. Use IS NULL:
DELETE FROM orders WHERE coupon IS NULL;
DELETE 5
NOT IN with a NULL deletes nothing
Now delete customers who have never ordered. Dan is the only one, and this looks right:
DELETE FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
DELETE 0
Zero rows, in both SQLite and PostgreSQL. The subquery returns the NULL from guest order 107, and id NOT IN (1, 2, 3, 5, NULL) can never be true, because the database can't rule out that Dan's id "equals" the unknown value. Use NOT EXISTS, which isn't affected by NULLs:
DELETE FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
RETURNING id, name;
id | name
---+-----
4 | Dan
(1 row)
The opposite failure is worse: WHERE NOT (status = 'paid') skips rows where status is NULL, so a "delete everything that isn't paid" leaves them behind. If NULL is possible, say what you want: WHERE status <> 'paid' OR status IS NULL.
Soft delete: when not to DELETE at all
Many applications never actually delete customer-facing data. Instead they mark it:
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMP;
UPDATE customers
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 3;
Queries then filter with WHERE deleted_at IS NULL (often through a view). You keep history, undo is a single UPDATE, and foreign keys never get in the way. The costs: every query must remember the filter, unique constraints need care (a "deleted" email still blocks a new signup with that email), and privacy laws may require you to really delete personal data on request. A common compromise: soft delete first, then a scheduled job hard-deletes rows older than 30 days.
MySQL error 1175: safe update mode
In MySQL Workbench, a delete without a key in the WHERE fails with:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
That's Workbench protecting you from deleting the whole table. The safest fix is to delete by primary key (find the ids with a SELECT, then DELETE ... WHERE id IN (...)). If you're sure, turn it off for the session only:
SET SQL_SAFE_UPDATES = 0;
DELETE FROM orders WHERE status = 'cancelled';
SET SQL_SAFE_UPDATES = 1;
It can also be turned off permanently in Preferences → SQL Editor (reconnect afterwards), but the session-only switch keeps the guard rail for everything else.
DELETE in MySQL, PostgreSQL, SQL Server, SQLite and Oracle
The basic DELETE FROM t WHERE ... is identical everywhere. The extras are not:
| Feature | MySQL | PostgreSQL | SQL Server | SQLite | Oracle |
|---|---|---|---|---|---|
| Delete with another table | DELETE t FROM t JOIN ... | DELETE FROM t USING ... | DELETE t FROM t JOIN ... | Subquery only | Subquery only |
| Return deleted rows | No (MariaDB: RETURNING) | RETURNING | OUTPUT deleted.* | RETURNING (3.35+) | RETURNING ... INTO (PL/SQL) |
| Limit rows deleted | LIMIT n | Subquery with LIMIT | TOP (n) | LIMIT n if compiled in | WHERE ROWNUM <= n |
FROM keyword | Required | Required | Optional | Required | Optional |
| Alias on target table | Yes | Yes | In the join form | Only with AS | Yes |
TRUNCATE TABLE | Yes (commits) | Yes (transactional) | Yes (transactional) | No | Yes (commits) |
| Foreign keys enforced | Yes (InnoDB) | Yes | Yes | Only after PRAGMA foreign_keys = ON | Yes |
Performance: indexes and big deletes
A DELETE has to find its rows first, exactly like a SELECT, so the same index rules apply. In SQLite, with an index on status:
CREATE INDEX idx_orders_status ON orders(status);
EXPLAIN QUERY PLAN DELETE FROM orders WHERE status = 'cancelled';
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_status (status=?)
EXPLAIN QUERY PLAN DELETE FROM orders WHERE coupon = 'XMAS';
QUERY PLAN
`--SCAN orders
The first uses the index; the second reads the whole table. A few other things make deletes slow:
- Unindexed foreign keys. Deleting a customer makes the database check
orders.customer_idfor references. Without an index on that column, every single deleted customer means a full scan oforders. PostgreSQL and SQL Server don't create that index for you (MySQL does). - Every index is updated. Each deleted row is also removed from every index on the table, so a heavily indexed table deletes more slowly.
- Huge single deletes. Use batches, or for "delete almost everything", copy the rows you want to keep into a new table and swap it in.
- Space isn't returned right away. Deleted rows leave free space inside the table file. PostgreSQL reclaims it with
VACUUM, SQLite withVACUUM, MySQL withOPTIMIZE TABLE.
Common DELETE mistakes
- Forgetting
WHERE, or running only the first line of a selected statement. Preview withSELECT, and wrap risky deletes inBEGIN...ROLLBACK/COMMIT. - Deleting by a non-unique value (a name) when you meant one row. Use the primary key.
= NULLinstead ofIS NULL: deletes nothing.NOT INover a column withNULLs: deletes nothing. UseNOT EXISTS.- Mixing
ANDandORwithout parentheses: deletes far more than you meant. - Date ranges that stop at midnight:
<= '2025-12-31'misses most of December 31 on a timestamp column. Use< '2026-01-01'. - Not knowing about
ON DELETE CASCADE: one deleted row can remove many more in other tables. - Assuming SQLite enforces foreign keys: it doesn't until
PRAGMA foreign_keys = ON. - Using
TRUNCATEin MySQL expecting to roll back: it commits immediately. - Deleting millions of rows in one go on a busy database: lock waits and a giant log. Batch it.
FAQ
What is the DELETE command in SQL?
DELETE is the SQL statement that removes rows from a table: DELETE FROM table WHERE condition;. It's part of DML (data manipulation language), alongside INSERT and UPDATE. It removes data, not the table; DROP TABLE removes the table.
How do I delete a row in SQL?
Filter on the row's primary key: DELETE FROM orders WHERE id = 104;. Run SELECT * FROM orders WHERE id = 104; first to check it's the right row.
How do I delete multiple rows in SQL?
Write a WHERE clause that matches all of them: WHERE id IN (101, 103), WHERE status = 'cancelled', WHERE ordered_at < '2026-01-01'. One statement deletes every matching row.
How do I delete all rows from a table?
DELETE FROM table_name; with no WHERE. For a large table, TRUNCATE TABLE table_name; is faster (not available in SQLite). Both keep the empty table.
Is TRUNCATE better than DELETE?
Only for emptying a whole table, where it's much faster. DELETE is the one to use when you need a WHERE, need triggers to fire, or want to be able to roll back in MySQL or Oracle.
Can I undo a DELETE in SQL?
Only if it ran inside a transaction that hasn't been committed: run ROLLBACK;. Once committed (and in autocommit mode every statement commits immediately), you need a backup or point-in-time recovery.
How do I delete with a JOIN?
MySQL and SQL Server: DELETE o FROM orders o JOIN customers c ON ... WHERE .... PostgreSQL: DELETE FROM orders o USING customers c WHERE .... SQLite and Oracle: use WHERE customer_id IN (SELECT ...), which also works in all the others.
Why does DELETE fail with a foreign key error?
Other rows still reference the row you're deleting. Delete the child rows first, or define the foreign key with ON DELETE CASCADE (delete them automatically) or ON DELETE SET NULL (keep them, clear the reference).
What is the difference between DELETE and DROP?
DELETE removes rows and keeps the table. DROP TABLE removes the table itself, structure and all.
Does DELETE reset the auto-increment counter?
No. After DELETE FROM t; the next insert continues from the old highest id. TRUNCATE resets it in MySQL and SQL Server; in PostgreSQL use TRUNCATE t RESTART IDENTITY.
Practice the WHERE clause first
A DELETE is only as good as its WHERE clause, and that's exactly what the free interactive lessons train: start with WHERE equals, then AND, IN, NULL and LIKE. They run in your browser on a real SQLite database with no signup. Then practise writing filters in the SQL practice problems, see more statements in the SQL query examples, and keep the SQL cheat sheet open for the syntax. If you're also changing rows rather than removing them, see how to use UPDATE.
When you're working on a real database, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server, where you can preview rows, check a table's foreign keys and run deletes inside a transaction.