SQL DELETE Statement
Chapter 11 of 40 · Estimated read time: 7 min
The DELETE statement removes one or more rows from a table. The table structure, columns, and constraints remain intact — only the data rows are removed.
⚠️ DELETE without WHERE removes every row. Unlike dropping a table, DELETE keeps the table structure but empties it completely when no WHERE clause is used. In most production databases there is no recycle bin — always preview with SELECT first.
Syntax
-- Delete specific rows (with WHERE — recommended)
DELETE FROM table_name
WHERE condition;
-- Delete ALL rows (without WHERE — use with caution)
DELETE FROM table_name;
The Safe Delete Workflow
-- Step 1: SELECT first — see exactly what will be deleted SELECT * FROM orders WHERE order_date < '2026-10-03'; -- Step 2: Count the rows to be deleted SELECT COUNT(*) FROM orders WHERE order_date < '2026-10-03'; -- If the count looks right, proceed -- Step 3: Delete DELETE FROM orders WHERE order_date < '2026-10-03'; -- Step 4: Verify SELECT COUNT(*) FROM orders;
Example 1 — Delete One Specific Row
-- Remove a product that has been discontinued DELETE FROM products WHERE product_id = 4; -- Ballpoint Pens -- Verify it is gone SELECT * FROM products WHERE product_id = 4; -- Returns: 0 rows
Example 2 — Delete Multiple Rows Matching a Condition
-- Remove all orders below ₹500 (treat as test/dummy data) DELETE FROM orders WHERE total_amount < 500; -- This removes: -- order 1003 (C01 / Notebook A5 / ₹495) -- order 1007 (C01 / Ballpoint Pens / ₹490) -- That is 2 rows deleted
Example 3 — Delete Using a Subquery
Delete rows based on a condition in another table. For example, remove all orders placed by customers from a particular city:
-- Delete all orders placed by customers from Mumbai DELETE FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE city = 'Mumbai' ); -- Removes order 1004 (Rohan Mehta, Mumbai, Ergonomic Chair)
Example 4 — Delete All Rows (Empty a Table)
-- Remove all rows but keep the table structure DELETE FROM orders; -- The orders table still exists with all its columns -- but now has 0 rows SELECT COUNT(*) FROM orders; -- Result: 0
DELETE vs TRUNCATE — Key Differences
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Can use WHERE? | ✅ Yes | ❌ No — removes all rows always |
| Speed on large tables | Slower — logs each row | Much faster — minimal logging |
| Can be rolled back? | ✅ Yes (inside a transaction) | ❌ No in most databases (auto-commits) |
| Resets AUTO INCREMENT? | No | ✅ Yes — counter resets to 1 |
| Triggers fired? | ✅ Yes — DELETE triggers fire | ❌ No triggers |
| Preserves table structure? | ✅ Yes | ✅ Yes |
-- TRUNCATE — faster way to empty a table (no WHERE possible) TRUNCATE TABLE orders;
When to use which: Use DELETE when you need to remove specific rows or want the safety of a rollback. Use TRUNCATE when you need to wipe an entire table as fast as possible (e.g., clearing a staging/temp table before reloading data).
Using Transactions to Safely Undo a Delete
-- Wrap DELETE in a transaction so you can undo if something goes wrong BEGIN; DELETE FROM orders WHERE total_amount < 500; -- Check: how many rows remain? SELECT COUNT(*) FROM orders; -- If the count looks wrong, undo: ROLLBACK; -- If everything looks right, make it permanent: COMMIT;
Get into the habit of wrapping destructive operations in a transaction during development. Once you COMMIT, the change is permanent. ROLLBACK is your undo button — but only before the COMMIT.
✏️ Practice Exercises
1. Delete the customer with customer_id = 'C05' from the customers table.
2. Delete all orders placed before October 5th, 2026. First run a SELECT to preview which rows will be removed.
3. What is the difference between DELETE FROM products and TRUNCATE TABLE products? Write both statements.
Show Answers
-- 1. DELETE FROM customers WHERE customer_id = 'C05'; -- 2. Preview first: SELECT * FROM orders WHERE order_date < '2026-10-05'; -- Then delete: DELETE FROM orders WHERE order_date < '2026-10-05'; -- 3. -- DELETE removes all rows but can be rolled back; slow on large tables DELETE FROM products; -- TRUNCATE removes all rows faster; cannot be rolled back; resets auto-increment TRUNCATE TABLE products;