DELETE

Sooner or later you need to remove data: a test account, an abandoned shopping cart, an order that was entered twice. The DELETE statement removes whole rows from a table.

Deleted rows are gone for good unless you have a backup or you are inside a transaction, so this lesson is as much about safety as about syntax. You will learn to delete exactly the right rows, deal with rows that other tables depend on, and decide when not to delete at all.

The basic DELETE

DELETE FROM names the table, and WHERE chooses the rows to remove. Divya signed up but never placed an order, and she has asked the shop to close her account:

DELETE FROM customers
WHERE customer_id = 12;

SELECT COUNT(*) AS customers_left
FROM customers;

customers_left
--------------
11
(1 row)

There is no column list in a DELETE, because it always removes complete rows. To empty a single column instead, use UPDATE ... SET column = NULL (see UPDATE).

WHERE decides everything

Just like UPDATE, a DELETE without WHERE affects every row:

DELETE FROM customers;   -- removes ALL customers

Protect yourself with the same habits:

1. Write a SELECT with the WHERE you plan to use, and read the rows it returns.
2. Change SELECT ... FROM into DELETE FROM, keeping the WHERE exactly the same.
3. For anything important, run it inside a transaction so you can still roll it back (see Transactions).

MySQL's safe-updates mode (SET sql_safe_updates = 1;, on by default in MySQL Workbench) also applies to DELETE: it refuses a delete whose WHERE does not use a key column, unless you add a LIMIT. The error is number 1175.

Rows that other rows depend on

Tables are connected. Every order points to a customer, and every order item points to an order and a product. These links are foreign keys (see Constraints & Keys). MySQL does not let you delete a row while other rows still point to it. Try to delete the cancelled order 105 while it still has an item, and MySQL stops you:

ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (...)

This protects you from orphan rows: order items that belong to an order that no longer exists. The fix is to delete the children first, then the parent:

SELECT o.order_id, o.status, oi.item_id, oi.product_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';

DELETE FROM order_items WHERE order_id = 105;
DELETE FROM orders WHERE order_id = 105;

SELECT COUNT(*) AS orders_left FROM orders;
SELECT COUNT(*) AS items_left FROM order_items;

order_id | status    | item_id | product_id
---------+-----------+---------+-----------
105      | cancelled | 7       | 9
(1 row)

orders_left
-----------
14
(1 row)

items_left
----------
21
(1 row)

A table can also be set up to delete children automatically with ON DELETE CASCADE. That is convenient, but powerful: one delete of a customer could silently remove years of orders. You will see how to choose this behaviour in Constraints & Keys.

Deleting with a subquery

The rows to delete can be chosen by looking at another table. This removes the items of every cancelled order, however many there are, without typing their IDs:

DELETE FROM order_items
WHERE order_id IN (
  SELECT order_id FROM orders WHERE status = 'cancelled'
);

SELECT order_id, COUNT(*) AS items
FROM order_items
WHERE order_id BETWEEN 104 AND 106
GROUP BY order_id;

order_id | items
---------+------
104      | 1
106      | 2
(2 rows)

Order 105 no longer has any items, so it is missing from the count. Orders 104 and 106 are untouched. Run the subquery on its own first to check which order IDs it returns.

Cleaning up old rows

A common job is removing data that is no longer useful, such as items left in shopping carts for weeks. Here a small table is created for the example, then every cart item added before May 2025 is deleted:

CREATE TABLE cart_items (
  cart_item_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  added_on DATE NOT NULL
);

INSERT INTO cart_items (cart_item_id, customer_id, product_id, added_on) VALUES
  (1, 2, 6, '2025-03-01'),
  (2, 5, 9, '2025-05-28'),
  (3, 8, 12, '2025-04-15'),
  (4, 10, 4, '2025-06-01');

DELETE FROM cart_items
WHERE added_on < '2025-05-01';

SELECT * FROM cart_items;

cart_item_id | customer_id | product_id | added_on
-------------+-------------+------------+-----------
2            | 5           | 9          | 2025-05-28
4            | 10          | 4          | 2025-06-01
(2 rows)

On a large MySQL table, deleting millions of rows in one statement can lock the table for a long time. MySQL lets you delete in small batches with ORDER BY and LIMIT, repeating the statement until no rows are left:

DELETE FROM cart_items
WHERE added_on < '2025-05-01'
ORDER BY added_on
LIMIT 1000;

Soft deletes: keeping the history

In many business systems, rows are never really deleted. Instead, the table has a column such as deleted_at (a date and time) or is_active (true or false), and "deleting" means setting it:

UPDATE customers
SET deleted_at = '2025-06-10 11:30:00'
WHERE customer_id = 12;

Every normal query then adds WHERE deleted_at IS NULL. This is called a soft delete. You keep old orders, invoices and reports correct, and you can restore a row that was removed by mistake. The cost is that every query must remember the extra condition, which a view can do for you. Note that a real privacy request to erase someone's personal data may still need a true DELETE or the personal columns to be cleared.

DELETE, TRUNCATE and DROP

Three statements sound similar but do very different things:

TRUNCATE and DROP are covered in ALTER, DROP & TRUNCATE.

Key points

• DELETE FROM table WHERE condition removes whole rows.
• Without WHERE, every row is deleted; run the matching SELECT first.
• MySQL refuses to delete a row that other rows still reference (error 1451): delete children first.
• The browser editor does not check foreign keys, but MySQL does.
• A subquery can choose the rows to delete; ORDER BY ... LIMIT deletes in batches in MySQL.
• Soft deletes (deleted_at, is_active) keep history and allow undo.

DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'pending');

DELETE FROM orders
WHERE status = 'pending';

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status;