MySQL DELETE

DELETE Operations

🔹 Basic DELETE Syntax

The DELETE statement requires the table name and typically a WHERE clause to specify which rows to remove. Without WHERE, all rows in the table will be deleted. Always test your WHERE condition with SELECT first to verify you're targeting the correct rows before executing the DELETE command.

-- Delete single user by ID
DELETE FROM users 
WHERE id = 10;

-- Delete by name
DELETE FROM users 
WHERE name = 'John Doe';

-- Delete by email
DELETE FROM customers 
WHERE email = '[email protected]';

🔹 DELETE with Conditions

Use WHERE clause with various conditions to delete specific rows based on multiple criteria. You can combine conditions using AND, OR, comparison operators, and pattern matching. This allows precise targeting of records for deletion. Complex conditions help you remove exactly the data you want while preserving everything else safely.

-- Delete old records
DELETE FROM logs 
WHERE created_date < '2023-01-01';

-- Delete with multiple conditions
DELETE FROM products 
WHERE stock = 0 AND discontinued = TRUE;

-- Delete using comparison
DELETE FROM users 
WHERE age < 18 OR status = 'Inactive';

-- Delete with pattern matching
DELETE FROM spam_emails 
WHERE subject LIKE '%lottery%';

🔹 DELETE with LIMIT

The LIMIT clause restricts the number of rows deleted, which is useful for batch processing or testing deletions on a small subset first. This provides an extra safety layer when removing large amounts of data. Use LIMIT to delete records gradually or to test your DELETE statement before applying it to all matching rows.

-- Delete only first 10 matching rows
DELETE FROM old_logs 
WHERE log_date < '2022-01-01' 
LIMIT 10;

-- Test delete on single row first
DELETE FROM test_data 
WHERE category = 'obsolete' 
LIMIT 1;

-- Batch delete in chunks
DELETE FROM archive 
WHERE processed = TRUE 
LIMIT 100;

🔹 DELETE vs TRUNCATE

DELETE removes rows one by one and can use WHERE clause, while TRUNCATE removes all rows at once and is faster but cannot be filtered. DELETE can be rolled back in transactions, but TRUNCATE cannot. Use DELETE for selective removal and TRUNCATE only when you need to empty an entire table quickly and permanently.

-- DELETE: Selective, slower, can rollback
DELETE FROM users WHERE status = 'Inactive';

-- DELETE ALL: Removes all rows, can rollback
DELETE FROM temp_data;

-- TRUNCATE: Faster, removes all rows, cannot rollback
TRUNCATE TABLE temp_data;

-- TRUNCATE resets auto-increment counter
TRUNCATE TABLE sessions;

🔹 DELETE with JOIN

You can delete rows from one table based on data in another table using JOIN. This is useful for removing related records or cleaning up orphaned data. JOIN-based deletes allow complex deletion logic based on relationships between tables, enabling sophisticated data cleanup operations across multiple related tables simultaneously.

-- Delete users who have no orders
DELETE users 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id 
WHERE orders.id IS NULL;

-- Delete old products with no sales
DELETE products 
FROM products 
LEFT JOIN sales ON products.id = sales.product_id 
WHERE sales.id IS NULL AND products.created_date < '2020-01-01';

🔹 DELETE Safety Best Practices

Always follow strict safety protocols when deleting data to prevent accidental data loss. Test your WHERE clause with SELECT first, use transactions for critical operations, maintain regular backups, and consider soft deletes for important data. Careful DELETE operations protect your database integrity and allow data recovery if mistakes occur.

-- ⚠️ EXTREME DANGER: Deletes ALL rows (no WHERE)
DELETE FROM users;

-- ✅ SAFE: Test with SELECT first
SELECT * FROM users WHERE last_login < '2020-01-01';
-- Review results, then delete

-- ✅ Use transaction for safety
START TRANSACTION;
DELETE FROM users WHERE last_login < '2020-01-01';
-- Check results
ROLLBACK; -- or COMMIT if correct

-- ✅ Soft delete (recommended for important data)
UPDATE users SET deleted = TRUE WHERE id = 5;