DELETE and TRUNCATE

Removing rows

DELETE removes rows from a table. It has the same shape as UPDATE: a table and a WHERE clause that decides which rows.

DELETE FROM customers WHERE id = 12;

SELECT COUNT(*) AS customers_left FROM customers;

+----------------+
| customers_left |
+----------------+
|             11 |
+----------------+

Liam (customer 12) had never placed an order, so nothing depended on him and the delete worked. Just as with UPDATE, the WHERE clause is the safety catch. DELETE FROM customers; with no WHERE removes every customer. Check the rows with a SELECT first, and consider a transaction for anything important.

Deleting by condition

The condition can match several rows. First look at what would go:

SELECT id, status FROM orders WHERE status = 'cancelled';

+----+-----------+
| id | status    |
+----+-----------+
|  6 | cancelled |
| 17 | cancelled |
+----+-----------+

Then delete them. Orders have items, so remove the items first, using a subquery to find them:

DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled');
DELETE FROM orders WHERE status = 'cancelled';
SELECT COUNT(*) AS orders_left FROM orders;

+-------------+
| orders_left |
+-------------+
|          18 |
+-------------+

The order matters, as the next section shows.

Foreign keys can say no

Try to delete a customer who has orders:

DELETE FROM customers WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

MySQL refuses with error 1451, because orders.customer_id still points to that customer. This is the foreign key doing its job: it prevents orphaned orders that refer to a customer who no longer exists.

You have three options. Delete the child rows first (as above), change them to point somewhere else, or define the foreign key with ON DELETE CASCADE so that deleting the parent automatically deletes its children. Cascade is convenient but dangerous when applied to important data. The foreign keys lesson explains the options.

TRUNCATE: empty a table fast

TRUNCATE TABLE removes all rows in one go. It is much faster on big tables, because it does not process each row, and it resets the AUTO_INCREMENT counter. A scratch table makes the difference visible:

CREATE TABLE scratch (id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(20));
INSERT INTO scratch (note) VALUES ('a'), ('b'), ('c');
DELETE FROM scratch;
INSERT INTO scratch (note) VALUES ('after delete');
SELECT * FROM scratch;

+----+--------------+
| id | note         |
+----+--------------+
|  4 | after delete |
+----+--------------+

After DELETE, the counter kept going, so the new row got id 4. Now truncate and insert again:

TRUNCATE TABLE scratch;
INSERT INTO scratch (note) VALUES ('after truncate');
SELECT * FROM scratch;

+----+----------------+
| id | note           |
+----+----------------+
|  1 | after truncate |
+----+----------------+

The counter was reset, so the id starts from 1 again. Differences between the two:

TRUNCATE TABLE customers;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`shop`.`orders`, CONSTRAINT `orders_ibfk_1`)

DROP: remove the table itself

DROP TABLE removes the table definition and all its data:

DROP TABLE scratch;

Use DROP TABLE IF EXISTS scratch; in scripts so that running them twice does not fail. Dropping a database with DROP DATABASE deletes every table in it. Neither can be undone without a backup.

Soft deletes

In many applications rows are not really deleted. Instead a column such as deleted_at is set, and every query filters on WHERE deleted_at IS NULL:

ALTER TABLE products ADD COLUMN deleted_at DATETIME NULL;
UPDATE products SET deleted_at = '2024-08-01 10:00:00' WHERE id = 15;
SELECT id, name FROM products WHERE deleted_at IS NULL AND category_id = 5;

+----+---------------------+
| id | name                |
+----+---------------------+
| 14 | Building Blocks Set |
+----+---------------------+

This keeps history (an old order can still show the name of a retired product), lets you undo mistakes, and avoids foreign key problems. The cost is that every query must remember the filter, and the data keeps growing. Views, a later lesson, can hide that filter.

Common mistakes

• DELETE without WHERE.
• Using DELETE to empty a huge table when TRUNCATE would be far faster.
• Expecting TRUNCATE to be reversible. It commits immediately in MySQL.
• Deleting parent rows before child rows and hitting foreign key errors.
• Using DROP when you meant DELETE.

Exercise

Delete all order_items that belong to order 20, then delete order 20 itself. Confirm that the order count is now 19.

DELETE FROM order_items WHERE order_id = 20;
DELETE FROM orders WHERE id = 20;
SELECT COUNT(*) AS orders_left FROM orders;

+-------------+
| orders_left |
+-------------+
|          17 |
+-------------+