SQL Delete

The DELETE statement removes one or more existing rows from a table. It is one of the four core data modification commands in SQL, alongside INSERT, UPDATE, and SELECT, and it is permanent: once the surrounding transaction commits, deleted rows are gone unless you have a backup. Knowing exactly which rows a DELETE will touch before you run it is one of the most important skills in SQL, because a missing or wrong WHERE clause can wipe out an entire table in a fraction of a second.

Overview: How DELETE Works

DELETE removes complete rows from a table. It never removes individual columns (use UPDATE to clear a column’s value instead), and it never removes the table itself (use DROP TABLE for that). Under the hood, the database engine executes a DELETE statement much like a SELECT with a WHERE clause, except that instead of returning matching rows, it removes them.

  • The engine locates the target table named after FROM.
  • It scans the table, or, if a suitable index exists on a column used in the WHERE clause, it uses an index seek instead of scanning every row, to evaluate the condition for each row.
  • Every row for which the condition evaluates to true is flagged for removal.
  • The engine checks any foreign key constraints that reference the affected rows, applying cascade, set-null, or restrict behavior as defined on the relationship.
  • Any BEFORE DELETE or AFTER DELETE triggers defined on the table fire.
  • The flagged rows are removed, and any indexes on the table are updated so they no longer reference the deleted rows.
  • The change becomes permanent when the transaction commits; until then it can typically be undone with ROLLBACK.

If you omit the WHERE clause entirely, the condition is treated as true for every row, so every row in the table is deleted. The table itself, its columns, and its indexes still exist afterward, only the data is gone. This differs from TRUNCATE TABLE (available in MySQL, PostgreSQL, and SQL Server, but not SQLite), which also empties a table but is typically faster and resets auto-increment counters, and it differs even more from DROP TABLE, which removes the table definition entirely.

Syntax

DELETE FROM table_name
WHERE condition;
Part Meaning
DELETE FROM table_name Identifies which table rows are removed from. The FROM keyword is required in standard SQL.
WHERE condition Optional, but almost always present. Only rows for which condition is true are deleted. Omitting it deletes every row.
RETURNING columns Optional clause (SQLite, PostgreSQL) that returns the deleted rows’ values back to the caller, useful for logging what was removed.

Standard DELETE does not support ORDER BY or LIMIT the way SELECT does. MySQL adds non-standard ORDER BY/LIMIT support to DELETE, but SQLite, PostgreSQL, and SQL Server do not. To delete only the first matching rows in portable SQL, filter with a subquery instead, as shown later in this lesson.

Examples

Example 1: Deleting a Single Row by Primary Key

This is the most common form: identify one row by its primary key and remove it.

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO tasks (id, title, status) VALUES
  (1, 'Write report', 'done'),
  (2, 'Review code', 'pending'),
  (3, 'Deploy app', 'pending'),
  (4, 'Fix bug', 'done');

DELETE FROM tasks WHERE id = 1;

SELECT * FROM tasks ORDER BY id;

Result:

id title status
2 Review code pending
3 Deploy app pending
4 Fix bug done

The WHERE id = 1 condition matches only the row for ‘Write report’, so that single row is removed while the other three remain untouched.

Example 2: Deleting Multiple Rows with a Compound Condition

Combining conditions with AND lets you target a more specific set of rows.

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  category TEXT NOT NULL
);

INSERT INTO inventory (item_id, item_name, quantity, category) VALUES
  (1, 'Keyboard', 15, 'Electronics'),
  (2, 'Notebook', 0, 'Stationery'),
  (3, 'Monitor', 0, 'Electronics'),
  (4, 'Pen', 200, 'Stationery'),
  (5, 'Webcam', 0, 'Electronics');

DELETE FROM inventory WHERE quantity = 0 AND category = 'Electronics';

SELECT * FROM inventory ORDER BY item_id;

Result:

item_id item_name quantity category
1 Keyboard 15 Electronics
2 Notebook 0 Stationery
4 Pen 200 Stationery

Rows 3 (‘Monitor’) and 5 (‘Webcam’) matched both conditions, quantity equal to 0 and category equal to ‘Electronics’, so both were deleted. Row 2 (‘Notebook’) has quantity 0 but belongs to ‘Stationery’, so it fails the AND condition and survives.

Example 3: Deleting Duplicate Rows with a Subquery

A realistic use case: removing duplicate rows while keeping the earliest occurrence of each. This is a common cleanup task on a signups or import table.

CREATE TABLE signups (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO signups (id, email) VALUES
  (1, 'a@example.com'),
  (2, 'b@example.com'),
  (3, 'a@example.com'),
  (4, 'c@example.com'),
  (5, 'b@example.com');

DELETE FROM signups
WHERE id NOT IN (
  SELECT MIN(id) FROM signups GROUP BY email
);

SELECT * FROM signups ORDER BY id;

Result:

id email
1 a@example.com
2 b@example.com
4 c@example.com

The subquery groups rows by email and picks the smallest id for each distinct address: 1 for a@example.com, 2 for b@example.com, and 4 for c@example.com. The outer DELETE then removes every row whose id is not in that set, ids 3 and 5, because those are the later duplicate entries.

How DELETE Works Step by Step

Unlike SELECT, which has a rich logical processing order (FROM, WHERE, GROUP BY, HAVING, SELECT list, ORDER BY, LIMIT), a standard DELETE only has two logical stages, plus engine bookkeeping around them:

  • FROM table_name identifies the single target table.
  • WHERE condition is evaluated once per row, exactly like the WHERE stage of a SELECT, to decide which rows are in scope.
  • Matching rows are flagged for removal, and any foreign key relationships are checked or cascaded.
  • Triggers defined on the table fire around the deletion.
  • The rows are physically removed and indexes are updated to stay consistent.
  • The change is durable only once the transaction commits; an open transaction can still be rolled back.

Common Mistakes

Mistake 1: Forgetting the WHERE Clause

The single most damaging DELETE mistake is leaving off the WHERE clause, which deletes every row instead of the one you meant.

CREATE TABLE session_logs (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  logged_at TEXT NOT NULL
);

INSERT INTO session_logs (id, user_id, logged_at) VALUES
  (1, 101, '2026-07-01'),
  (2, 102, '2026-07-05'),
  (3, 101, '2026-07-10');

DELETE FROM session_logs;

SELECT COUNT(*) AS remaining_rows FROM session_logs;

Result: remaining_rows is 0. Because this statement has no WHERE clause, every row was removed, not just the one intended. The corrected version restricts the deletion with a condition:

CREATE TABLE session_logs (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  logged_at TEXT NOT NULL
);

INSERT INTO session_logs (id, user_id, logged_at) VALUES
  (1, 101, '2026-07-01'),
  (2, 102, '2026-07-05'),
  (3, 101, '2026-07-10');

DELETE FROM session_logs WHERE logged_at < '2026-07-05';

SELECT * FROM session_logs ORDER BY id;

Result: two rows remain, id 2 (‘2026-07-05’) and id 3 (‘2026-07-10’). Only id 1, logged before ‘2026-07-05’, is removed.

Mistake 2: Using MySQL-Style ORDER BY / LIMIT on DELETE

Developers coming from MySQL sometimes try to limit how many rows a DELETE affects directly on the statement:

DELETE FROM employees
ORDER BY salary ASC
LIMIT 1;

This fails on SQLite (and on PostgreSQL and SQL Server) because standard DELETE does not accept ORDER BY or LIMIT. The portable fix is to move that logic into a subquery that selects just the target row’s key, and delete based on membership in that set:

DELETE FROM employees
WHERE id IN (
  SELECT id FROM employees ORDER BY salary ASC LIMIT 1
);

Here the LIMIT is legal because it belongs to the inner SELECT, not to the outer DELETE. The subquery finds the id of the lowest-paid employee, and the outer statement deletes only that one row.

Mistake 3: Assuming DELETE Resets Auto-Increment Counters

Deleting every row from a table does not reset an AUTOINCREMENT primary key back to 1 in SQLite (it tracks the highest id ever used in an internal sqlite_sequence table), and the equivalent is true of AUTO_INCREMENT in MySQL and identity columns in SQL Server. If your application logic assumes ids restart after a DELETE, it will be surprised when new rows continue from where the old ones left off. Only TRUNCATE (where supported) typically resets that counter.

Best Practices

  • Run the equivalent SELECT with the same WHERE clause first, to see exactly which rows will be affected, before running the DELETE.
  • Wrap interactive DELETE statements in an explicit transaction so you can ROLLBACK if the condition turns out to be wrong.
  • Add indexes on columns that frequently appear in DELETE WHERE conditions to avoid full table scans on large tables.
  • Prefer a soft delete, an is_deleted or deleted_at column updated instead of removing the row, when you need to retain history or support undo; reserve real DELETE for data you truly never need again.
  • Be explicit about foreign key behavior (ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT) so related rows in other tables are handled the way you intend, not left orphaned or silently blocked.
  • Take a backup or snapshot before large bulk deletes in production systems.
  • Use a RETURNING clause where supported to capture exactly what was removed, for auditing.

Practice Exercises

  1. Using the inventory table from Example 2, write a DELETE statement that removes every row where quantity is 0, regardless of category. Hint: drop the category condition from Example 2’s query. Expected result: only ‘Keyboard’ and ‘Pen’ remain.
  2. Using the signups table structure from Example 3, imagine a sixth row (6, 'c@example.com') is inserted after the original five. Write out which id the same deduplication DELETE pattern would remove for the ‘c@example.com’ address, and why.
  3. Write a DELETE statement against an employees table (columns: id, name, department, salary, hire_date) that removes every employee whose department is ‘Marketing’. Then write the SELECT you would run first to preview exactly which rows it will affect.

Summary

  • DELETE FROM table WHERE condition removes matching rows; omitting WHERE deletes every row in the table.
  • The engine evaluates WHERE per row much like a SELECT’s WHERE stage, then removes matching rows, checks foreign keys, fires triggers, and updates indexes.
  • Combine conditions with AND/OR, or use subqueries to delete duplicates or rows related to data in another table.
  • Standard SQL and SQLite do not support ORDER BY/LIMIT directly on DELETE, unlike MySQL; use a subquery to achieve the same effect portably.
  • Always preview with a matching SELECT and consider transactions before deleting in production.
  • DELETE removes rows, not columns or the table itself; use UPDATE, TRUNCATE, or DROP TABLE for those other cases respectively.