MySQL DELETE

Overview

The DELETE statement removes rows from a table. This chapter covers how to delete specific rows, use conditions, and implement soft delete patterns.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Basic DELETE

Delete Specific Rows

-- Delete single row
DELETE FROM users WHERE id = 1;

-- Delete multiple rows
DELETE FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';

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

Delete All Rows

-- Delete all rows
DELETE FROM temporary_data;

-- Delete all rows (DANGEROUS!)
DELETE FROM logs;  -- All records deleted

-- Better: Use TRUNCATE for better performance
TRUNCATE TABLE logs;

DELETE with Conditions

WHERE Clause

-- Delete based on ID
DELETE FROM users WHERE id IN (1, 2, 3, 4, 5);

-- Delete with pattern
DELETE FROM logs WHERE message LIKE '%error%' AND created_at < '2024-01-01';

-- Delete with subquery
DELETE FROM orders WHERE user_id IN (
    SELECT user_id FROM users WHERE status = 'banned'
);

-- Delete with EXISTS
DELETE FROM products WHERE NOT EXISTS (
    SELECT 1 FROM categories WHERE categories.id = products.category_id
);

ORDER BY and LIMIT

-- Delete with ORDER BY
DELETE FROM logs ORDER BY created_at LIMIT 1000;

-- Delete oldest 100 records
DELETE FROM audit_log 
ORDER BY created_at ASC 
LIMIT 100;

-- Delete with ORDER BY and LIMIT
DELETE FROM sessions 
ORDER BY last_activity ASC 
LIMIT 1000
WHERE expired = 1;

JOIN in DELETE

-- Delete with INNER JOIN
DELETE u FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'cancelled';

-- Delete with LEFT JOIN
DELETE p FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE c.id IS NULL;

-- Delete with multiple tables
DELETE o, oi FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'cancelled';

Soft Delete

Add Deleted Column

-- Add soft delete column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE users ADD COLUMN deleted_by INT DEFAULT NULL;

-- Create index for performance
CREATE INDEX idx_users_deleted ON users(deleted_at);

Implement Soft Delete

-- Instead of DELETE, use UPDATE
UPDATE users SET 
    deleted_at = NOW(),
    deleted_by = 1,
    status = 'deleted'
WHERE id = 1;

-- Query with soft delete filter
SELECT * FROM users WHERE deleted_at IS NULL;

-- Include deleted records
SELECT * FROM users;  -- Shows all including deleted

-- Include deleted with filter
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Soft Delete Benefits

-- Data preserved for audit
SELECT * FROM users WHERE id = 1;  -- Shows record even if deleted

-- Recovery possible
UPDATE users SET deleted_at = NULL, status = 'active' WHERE id = 1;

-- No foreign key issues
-- Related records remain intact

Bulk Delete Operations

Chunked Deletion

-- Delete in chunks to avoid locking
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
-- Repeat until all deleted

-- Use stored procedure
DELIMITER //
CREATE PROCEDURE CleanupOldLogs()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    
    REPEAT
        DELETE FROM logs 
        WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR) 
        LIMIT batch_size;
        
        UNTIL ROW_COUNT() = 0
    END REPEAT;
END //
DELIMITER ;

CALL CleanupOldLogs();

Scheduled Cleanup

#!/bin/bash
# cleanup.sh - Delete old logs in chunks

while true; do
    mysql -u root -p -e "
        DELETE FROM logs 
        WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY) 
        LIMIT 10000;
    "
    
    if [ $? -ne 0 ]; then
        break
    fi
    
    sleep 5
done

DELETE vs TRUNCATE

| Aspect |-------------|--------|----------| | Syntax | WHERE clause | Performance | AUTO_INCREMENT | Triggers | Transaction | Rollback

Safe DELETE Practices

Always Use WHERE

-- DANGEROUS: Deletes all rows
DELETE FROM users;  -- Don't do this!

-- SAFE: With WHERE clause
DELETE FROM users WHERE id = 1;

Backup Before Delete

-- Create backup
CREATE TABLE deleted_users_backup AS 
SELECT * FROM users WHERE id IN (1, 2, 3);

-- Or use transaction
START TRANSACTION;
DELETE FROM users WHERE id IN (1, 2, 3);
-- Verify
-- COMMIT; or ROLLBACK;

Test with SELECT First

-- First, verify what will be deleted
SELECT * FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';
SELECT COUNT(*) FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';

-- Then delete
DELETE FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';

Common DELETE Patterns

Delete Old Records

-- Delete records older than 1 year
DELETE FROM audit_log WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete records older than specific date
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete duplicate records
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id < u2.id AND u1.email = u2.email;

Delete Based on Related Tables

-- Delete orphaned records
DELETE FROM products WHERE category_id NOT IN (SELECT id FROM categories);

-- Delete with cascade simulation
DELETE o FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

-- Delete old cart items
DELETE FROM cart_items 
WHERE cart_id IN (
    SELECT id FROM carts WHERE updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
);

Troubleshooting

Common Errors

-- Foreign key constraint error
-- Error: Cannot delete or update a parent row
-- Solution: Delete child records first or use ON DELETE CASCADE

-- Lock wait timeout
-- Error: Lock wait timeout exceeded
-- Solution: Delete in smaller batches

-- Table is read only
-- Error: Table is read only
-- Solution: Check table engine and permissions

Debug DELETE

-- Check affected rows
SELECT ROW_COUNT();

-- Show tables using row
SHOW OPEN TABLES WHERE In_use > 0;

-- Check locks
SHOW ENGINE INNODB STATUS;

Summary

DELETE statement includes:

• Basic Syntax: Remove rows with WHERE
• WHERE Clause: Filter rows to delete
• JOIN Deletes: Delete from multiple tables
• Soft Delete: Preserve data with deleted_at
• Bulk Operations: Chunked deletion
• Safety: Always use WHERE, backup first
• TRUNCATE: Faster alternative for full table

---

Previous: UPDATE

Next: LIKE Clause