DELETE Statement
Remove rows from your tables with DELETE β and learn how to avoid accidental data loss.
- Targeted Deletes β Remove specific rows with WHERE
- Safe Patterns β Preview before deleting
- DELETE vs TRUNCATE vs DROP β Know the differences Deleted data is usually gone forever. Always backup first.
What Is DELETE?
DfDELETE Statement
The DELETE statement removes existing records from a table. It locates rows using a WHERE condition and permanently deletes them. Without WHERE, all rows are removed.
DELETE FROM customers WHERE id = 5;
Basic Syntax
DfDELETE Syntax
DELETE FROM table_name WHERE condition;
The WHERE clause is optional but strongly recommended. Without it, all rows are deleted.
| Component | Purpose | Required |
|---|---|---|
DELETE FROM table_name | Specifies the target table | Yes |
WHERE condition | Filters which rows to delete | No (dangerous) |
-- Delete a specific customer
DELETE FROM customers WHERE id = 5;
-- Delete all customers from a city
DELETE FROM customers WHERE city = 'New York';
-- Delete with multiple conditions
DELETE FROM orders
WHERE order_date < '2023-01-01'
AND status = 'Cancelled';
Delete with Subqueries
Subqueries let you delete rows based on data in other tables.
-- Delete customers who have never placed an order
DELETE FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id FROM orders
);
-- Delete old audit logs
DELETE FROM audit_logs
WHERE created_at < (
SELECT DATE_SUB(MAX(created_at), INTERVAL 90 DAY)
FROM audit_logs
);
-- Safer: NOT EXISTS avoids NULL pitfalls
DELETE FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
Safe Delete Pattern
Follow a three-step process: preview, execute, verify.
-- Step 1: Preview rows to be deleted
SELECT * FROM customers WHERE city = 'Chicago';
-- Step 2: Delete the rows
DELETE FROM customers WHERE city = 'Chicago';
-- Step 3: Verify deletion
SELECT * FROM customers WHERE city = 'Chicago';
DfTransaction Safety
Wrapping DELETE in a transaction allows rollback if the result is unexpected. Use BEGIN TRANSACTION, run DELETE, inspect the result, then COMMIT or ROLLBACK.
BEGIN TRANSACTION;
DELETE FROM orders
WHERE order_date < '2022-01-01'
AND status = 'Archived';
-- Check how many rows were deleted
SELECT ROW_COUNT();
-- COMMIT; -- if satisfied
-- ROLLBACK; -- if something went wrong
DELETE vs TRUNCATE vs DROP
Understanding when to use each operation prevents data loss and improves performance.
DfThree Removal Methods
DELETE removes specific rows with WHERE support. TRUNCATE removes all rows without WHERE and resets identity columns. DROP removes the entire table structure and data.
| Operation | Removes Data | Keeps Table | Can Use WHERE | Speed | Logging |
|---|---|---|---|---|---|
| DELETE | Yes | Yes | Yes | Slow | Full |
| TRUNCATE | Yes | Yes | No | Fast | Minimal |
| DROP | Yes | No | No | Fastest | Metadata only |
-- Use DELETE when you need row-level control
DELETE FROM employees WHERE status = 'Terminated';
-- Use TRUNCATE when removing ALL rows quickly
TRUNCATE TABLE temp_data;
-- Use DROP when removing the entire table
DROP TABLE old_archive;
Batch Deletes
For large datasets, delete in batches to avoid long locks and transaction log growth.
-- Delete in batches of 1000 rows
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;
-- Repeat until no rows are affected
-- Check with SELECT COUNT(*) first
SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01';
DfBatch Delete
A batch delete removes rows in chunks using LIMIT. This prevents long-running transactions, reduces lock contention, and keeps log growth manageable.
Common Pitfalls
| Pitfall | Consequence | Solution |
|---|---|---|
| Missing WHERE clause | Deletes all rows | Always add WHERE |
| Ignoring foreign keys | Constraint violation error | Check dependent tables first |
| No transaction wrap | Cannot undo deletion | Use BEGIN/COMMIT/ROLLBACK |
| Deleting in production | Data loss | Use staging environment |
-- Dangerous: Deletes ALL rows
DELETE FROM employees;
-- Safe: Targeted delete
DELETE FROM employees WHERE department = 'Temp';
Performance Considerations
- Index the WHERE columns β speeds up row lookup
- Batch large deletes β reduces lock duration
- Check foreign keys β dependent rows may block deletion
- Monitor transaction logs β DELETE generates significant log entries
- Use EXPLAIN β verify the query plan before deleting
-- Check the execution plan
EXPLAIN DELETE FROM employees
WHERE department = 'Engineering';
Practice Exercises
Exercise 1: Write a DELETE to remove all products with zero stock.
-- Solution
DELETE FROM products
WHERE stock_quantity = 0;
Exercise 2: Delete inactive customers who have never placed an order.
-- Solution
DELETE FROM customers
WHERE status = 'Inactive'
AND customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);
Exercise 3: Use a transaction to safely delete old order items.
-- Solution
BEGIN TRANSACTION;
DELETE FROM order_items
WHERE order_id IN (
SELECT order_id FROM orders
WHERE order_date < '2022-01-01'
);
-- Verify before committing
SELECT COUNT(*) AS remaining
FROM order_items
WHERE order_id IN (
SELECT order_id FROM orders
WHERE order_date < '2022-01-01'
);
-- COMMIT; -- if count is 0
Summary
Key Takeaways
- DELETE removes rows from a table permanently outside transactions
- Always include a WHERE clause unless deleting all rows intentionally
- Preview with SELECT before running DELETE
- TRUNCATE is faster for removing all rows but lacks WHERE support
- DROP removes the entire table structure and data
- Use transactions for safety on critical deletions
- Batch large deletes to avoid lock contention and log bloat
| Statement | Data | Table | WHERE | Reversible |
|---|---|---|---|---|
| DELETE | Removed | Kept | Yes | Transaction only |
| TRUNCATE | Removed | Kept | No | Some engines only |
| DROP | Removed | Removed | No | No |