SQL DELETE and TRUNCATE

DELETE removes rows from a table; TRUNCATE empties a table completely. Both are permanent once committed, so the habits from the UPDATE lesson matter even more here: check the WHERE with a SELECT first, and use a transaction when the data is valuable.

DELETE syntax

DELETE FROM table_nameWHERE condition;

Sample data

Delete one row

Product 9 (Monitor Arm) has never been ordered and is discontinued:

DELETE FROM productsWHERE product_id = 9; SELECT product_id, product_name FROM products;

Delete several rows

A WHERE that matches several rows deletes them all. Remove every line of order 1001:

DELETE FROM order_itemsWHERE order_id = 1001; SELECT * FROM order_items WHERE order_id IN (1001, 1002);

Check first with SELECT

Before deleting, run the same WHERE as a SELECT:

SELECT * FROM orders WHERE status = 'Cancelled';

If the result is what you expect, change SELECT * to DELETE and run it.

Foreign keys block orphan rows

Order 1004 still has an order line. The foreign key from order_items to orders stops you from deleting the order and leaving the line pointing at nothing:

DELETE FROM orders WHERE order_id = 1004;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`))

Delete the child rows first, then the parent:

DELETE FROM order_items WHERE order_id = 1004;DELETE FROM orders WHERE order_id = 1004;SELECT order_id, status FROM orders WHERE status = 'Cancelled';

Delete with a subquery

Delete customers who have never placed an order:

DELETE FROM customersWHERE customer_id NOT IN (SELECT customer_id FROM orders); SELECT customer_id, customer_name FROM customers;

Delete with JOIN (MySQL)

Delete the order lines of every delivered order placed before February 2025:

DELETE oiFROM order_items oiJOIN orders o ON o.order_id = oi.order_idWHERE o.status = 'Delivered' AND o.order_date < '2025-02-01'; SELECT COUNT(*) AS remaining_lines FROM order_items;

Delete with ORDER BY and LIMIT

MySQL can delete a limited number of rows. This is useful for removing huge amounts of old data in small batches:

DELETE FROM order_itemsORDER BY order_idLIMIT 2; SELECT COUNT(*) AS remaining_lines FROM order_items;

TRUNCATE TABLE

TRUNCATE TABLE removes all rows at once and resets AUTO_INCREMENT counters:

CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(50));INSERT INTO audit_log (msg) VALUES ('login'), ('logout');TRUNCATE TABLE audit_log;INSERT INTO audit_log (msg) VALUES ('fresh start');SELECT * FROM audit_log;

DELETE vs TRUNCATE vs DROP

DELETE FROM order_items WHERE product_id IN (SELECT product_id FROM products WHERE stock = 0);DELETE FROM products WHERE stock = 0;SELECT product_id, product_name, stock FROM products;

DELETE FAQ