DELETE and TRUNCATE
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Some rows have to go: a cancelled cinema booking, a spam sign-up, an expired OTP. DELETE removes rows from a table, and like UPDATE it uses WHERE to decide which ones.
DELETE ... WHERE
DELETE removes whole rows from a table. DELETE FROM table WHERE condition removes every row where the condition is true; the table and its other rows stay.
Example 1 removes the cancelled bookings of a cinema show and leaves the other rows alone.
Example 1 · Cinema: remove cancelled bookings
CREATE TABLE show_bookings ( booking_id INT AUTO_INCREMENT PRIMARY KEY, seat CHAR(3) NOT NULL, customer VARCHAR(30) NOT NULL, status ENUM('BOOKED', 'CANCELLED') NOT NULL);
INSERT INTO show_bookings (seat, customer, status) VALUES ('F07', 'Asha', 'BOOKED'), ('F08', 'Ravi', 'CANCELLED'), ('F09', 'Meena', 'BOOKED'), ('G01', 'Kabir', 'CANCELLED');
-- 1. Check which rows will goSELECT * FROM show_bookings WHERE status = 'CANCELLED' ORDER BY booking_id;
-- 2. Same WHERE, now as a DELETEDELETE FROM show_bookings WHERE status = 'CANCELLED';
SELECT * FROM show_bookings ORDER BY booking_id;+------------+------+----------+-----------+ | booking_id | seat | customer | status | +------------+------+----------+-----------+ | 2 | F08 | Ravi | CANCELLED | | 4 | G01 | Kabir | CANCELLED | +------------+------+----------+-----------+ +------------+------+----------+--------+ | booking_id | seat | customer | status | +------------+------+----------+--------+ | 1 | F07 | Asha | BOOKED | | 3 | F09 | Meena | BOOKED | +------------+------+----------+--------+
What this code does
- The
SELECTshows the two cancelled bookings first. TheDELETEuses the sameWHERE, so exactly those two rows go and seats F08 and G01 are free again. DELETE FROMhas no column list: it always removes whole rows. The other two bookings keep their ids 1 and 3; ids are never renumbered.
To clear just one value, such as a phone number, use UPDATE ... SET phone = NULL instead. Writing DELETE * FROM orders ... is a syntax error (ERROR 1064). The check-first habit from UPDATE matters even more here, because a deleted row is gone. Run a SELECT with the same WHERE, look at the rows, then change SELECT ... FROM into DELETE FROM.
DELETE: without a key column in the WHERE or a LIMIT, it refuses with error 1175.DELETE without WHERE
DELETE FROM table; with no WHERE removes every row. The table, its columns, keys and constraints stay, ready for new rows.
Example 2 empties a shopping cart with DELETE and no WHERE. The table itself stays.
Example 2 · Empty a shopping cart, keep the table
CREATE TABLE cart_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, qty INT NOT NULL);
INSERT INTO cart_items (product_id, qty) VALUES (6, 2), (9, 1), (14, 1);
DELETE FROM cart_items;SELECT COUNT(*) AS items_left FROM cart_items;
INSERT INTO cart_items (product_id, qty) VALUES (11, 1);SELECT * FROM cart_items;+------------+ | items_left | +------------+ | 0 | +------------+ +---------+------------+-----+ | item_id | product_id | qty | +---------+------------+-----+ | 4 | 11 | 1 | +---------+------------+-----+
What this code does
DELETE FROM cart_items;with noWHEREremoves all three rows.COUNT(*)proves the table is empty, and the nextINSERTshows the table itself still works.- The new row gets id 4, not 1:
DELETEdoes not reset theAUTO_INCREMENTcounter.
Use DELETE without WHERE on purpose to empty a small working table, such as a shopping cart or a temporary list. By mistake, it empties a real table in one keystroke, exactly like UPDATE without WHERE. An empty result prints nothing in the mysql client, so to prove a table is empty, count its rows: *`SELECT COUNT() FROM table** returns the number of rows. The Aggregates module covers COUNT` and its friends.
TRUNCATE and how it differs
TRUNCATE TABLE empties a table completely and much faster than DELETE, because MySQL drops the data and creates the table again, empty. It has no WHERE, cannot be rolled back and restarts AUTO_INCREMENT at 1.
Example 3 empties a table two ways and compares the next id. Example 4 shows MySQL refusing TRUNCATE on a parent table.
Example 3 · DELETE versus TRUNCATE: the id counter
CREATE TABLE otp_codes ( otp_id INT AUTO_INCREMENT PRIMARY KEY, phone CHAR(10) NOT NULL, code CHAR(6) NOT NULL);
INSERT INTO otp_codes (phone, code) VALUES ('9876500001', '482910'), ('9876500002', '113377'), ('9876500003', '905126');
DELETE FROM otp_codes;INSERT INTO otp_codes (phone, code) VALUES ('9876500004', '640218');SELECT * FROM otp_codes;
TRUNCATE TABLE otp_codes;INSERT INTO otp_codes (phone, code) VALUES ('9876500005', '771045');SELECT * FROM otp_codes;+--------+------------+--------+ | otp_id | phone | code | +--------+------------+--------+ | 4 | 9876500004 | 640218 | +--------+------------+--------+ +--------+------------+--------+ | otp_id | phone | code | +--------+------------+--------+ | 1 | 9876500005 | 771045 | +--------+------------+--------+
What this code does
- After
DELETE, the next OTP gets id 4: the counter remembers the old rows. AfterTRUNCATE, the next one gets id 1, because the table was created again empty. - Both emptied the table.
TRUNCATEis the fast choice for clearing a big log or staging table, as long as nothing points to it and you will not need to undo it.
Example 4 · TRUNCATE is refused on a parent table
TRUNCATE TABLE customers;ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`practice`.`orders`, CONSTRAINT `orders_ibfk_1`)
What this code does
orders.customer_idis a foreign key tocustomers, so InnoDB will not truncatecustomersat all. It would refuse even iforderswere empty.- The message names your own database, here
practice, and the constraint MySQL generated (orders_ibfk_1), so your text can differ slightly; that is why it is a sample.
TRUNCATE leaves the same empty table as DELETE FROM name, but the rules differ. It is a DDL statement (one that changes table structure), so MySQL commits it at once and it cannot be undone with ROLLBACK. InnoDB also refuses it on a table that another table's foreign key points to, even if that child table is empty. DROP TABLE name goes one step further: it removes the rows and the table itself. After DROP, a SELECT fails with "Table doesn't exist".
DELETE: some or all rows,WHEREallowed, can be rolled back, keeps the id counter, row by row.TRUNCATE: all rows, noWHERE, cannot be rolled back, ids start at 1, very fast.DROP: the rows and the table structure are gone.
Find the bug
Find the wrong line
Each statement should remove only some rows. One line is wrong.
Remove only the cancelled orders.
Round 1 of 2Deleting rows that other tables reference
A parent row is a row that another table's foreign key points to, such as a customer who has orders. With the default rule (RESTRICT), MySQL refuses to delete it.
Customer 1 has orders, so Example 5 shows MySQL refusing to delete that customer. Example 6 removes a cancelled order, children first.
Example 5 · Deleting a customer who has orders
SELECT order_id, status FROM orders WHERE customer_id = 1 ORDER BY order_id;
DELETE FROM customers WHERE customer_id = 1;+----------+-----------+ | order_id | status | +----------+-----------+ | 1 | DELIVERED | | 3 | DELIVERED | | 10 | SHIPPED | +----------+-----------+ ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
What this code does
- Asha Rao (customer 1) has three orders, so she is a parent row. With the default
RESTRICTrule, MySQL refuses theDELETEand nothing changes. - The message names the child table and the constraint. Your database name appears where this shows
practice.
Example 6 · Children first: remove a cancelled order completely
-- Meena Iyer (customer 3) placed one order, order 4, and cancelled it.SELECT * FROM order_items WHERE order_id = 4;
DELETE FROM order_items WHERE order_id = 4; -- 1. the itemsDELETE FROM orders WHERE order_id = 4; -- 2. the orderDELETE FROM customers WHERE customer_id = 3; -- 3. the customer
SELECT COUNT(*) AS items_left FROM order_items WHERE order_id = 4;SELECT COUNT(*) AS orders_left FROM orders WHERE customer_id = 3;SELECT customer_id, name FROM customers WHERE customer_id BETWEEN 2 AND 4 ORDER BY customer_id;+----------+------------+----------+------------+ | order_id | product_id | quantity | unit_price | +----------+------------+----------+------------+ | 4 | 3 | 1 | 69900.00 | +----------+------------+----------+------------+ +------------+ | items_left | +------------+ | 0 | +------------+ +-------------+ | orders_left | +-------------+ | 0 | +-------------+ +-------------+-------------+ | customer_id | name | +-------------+-------------+ | 2 | Ravi Kumar | | 4 | Kabir Singh | +-------------+-------------+
What this code does
- Each
DELETEremoves rows that nothing points to any more: first the item (it points to the order), then the order (it points to the customer), then the customer. - In the reverse order, step 2 or 3 would fail with error 1451. For most real shops, marking the order
CANCELLEDand keeping it is better than deleting history.
The refusal protects you. Deleting the customer would leave orders that belong to nobody. You have three honest ways forward:
- Children first: delete the order items, then the orders, then the customer, in that order.
ON DELETE CASCADEon the foreign key makes MySQL delete the children for you. It is convenient, but oneDELETEcan then remove far more rows than you see.- Do not delete at all: for orders, payments and marks, history usually matters. Mark the row instead, such as
status = 'CANCELLED'or anis_activecolumn set to 0. This is called a soft delete.
Next step
Check your understanding
12 quick questions