SQL guide · Changing data

SQL DELETE: Remove Rows From a Table

The DELETE statement removes rows from a table. You name the table and add a WHERE clause that selects the rows to remove. The table structure, its indexes and its constraints all stay in place, so DELETE only affects data. If you want to drop the table itself, use DROP TABLE.

As with UPDATE, the WHERE clause is critical. A DELETE without it removes every row in the table. Writing the matching SELECT first, confirming the row count, and running the delete inside a transaction are the standard habits that prevent expensive mistakes. After the delete you can check how many rows were affected before committing.

Deletes are checked against foreign keys. If other tables reference the rows you are removing, the statement fails unless the key was defined with ON DELETE CASCADE or ON DELETE SET NULL. For emptying a whole table quickly, TRUNCATE TABLE is usually faster because it does not log each row, but it cannot take a WHERE clause.

Basic syntax

DELETE FROM table_name
WHERE condition;

Only rows matching the WHERE condition are removed. Without WHERE, all rows are deleted but the table remains. The FROM keyword is required in most databases, although SQL Server lets you omit it.

SQL DELETE examples

Delete a single row

Deleting by primary key removes exactly one record. This is the safest form because the key is unique, so no other rows can match.

DELETE FROM customers
WHERE customer_id = 42;

Delete rows matching a condition

Several conditions can be combined. This removes cancelled orders from before 2024, which is a typical clean-up job. Check the row count before committing.

DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2024-01-01';

Delete using a subquery

A subquery can find rows in another table. This removes customers who have never placed an order by testing for the absence of related rows with NOT EXISTS.

DELETE FROM customers
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = customers.customer_id
);

Preview first, then delete in a transaction

Running a SELECT with the same condition shows what would be removed. Wrapping the delete in a transaction lets you roll back if the affected row count is not what you expected.

SELECT COUNT(*) FROM orders WHERE status = 'draft';

BEGIN;
DELETE FROM orders WHERE status = 'draft';
-- check the reported row count, then:
COMMIT;

Where SQL dialects differ

PostgreSQL: Supports DELETE ... USING other_table WHERE ... to delete based on a join, and RETURNING to see the removed rows. There is no LIMIT on DELETE.

MySQL: Supports multi-table deletes such as DELETE o FROM orders o JOIN customers c ON ..., and ORDER BY ... LIMIT n for single-table deletes. In safe update mode, a delete without a key in WHERE is blocked.

SQL Server: Supports DELETE TOP (n), joins with DELETE t FROM t INNER JOIN ..., and OUTPUT deleted.* to capture removed rows. FROM is optional after DELETE.

SQLite: Has no joined delete, so use WHERE id IN (SELECT ...). A DELETE without WHERE is optimised as a truncate. LIMIT on DELETE is only available in builds compiled with the right option, and RETURNING arrived in 3.35.0.

Oracle: Uses DELETE FROM t WHERE ... with subqueries instead of joins, and RETURNING ... INTO to capture values. Limit rows with WHERE ROWNUM <= n or FETCH FIRST n ROWS ONLY in newer versions.

Common mistakes

Running DELETE FROM table_name without WHERE and removing every row. Always write and check the WHERE clause first.
Not running a matching SELECT beforehand, so the wrong set of rows gets deleted. Preview the rows, then delete.
Ignoring foreign keys. Deleting a parent row fails if child rows exist, unless cascading is configured, and cascades can remove far more data than you expect.
Using DELETE to empty a very large table. It logs every row and is slow, so TRUNCATE TABLE is a better choice when you want everything gone and no WHERE is needed.
Using NOT IN with a subquery that can return NULL. The comparison then matches nothing, so use NOT EXISTS instead.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator