MySQL DELETE Statement

The DELETE statement is used to remove existing records from a table.This operation is irreversible (unless you have backups), so always use it with extreme caution. Always include a WHERE clause unless you intend to delete all records.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Delete Specific Records

-- Delete employee with id 5
DELETE FROM employees 
WHERE id = 5;

-- Delete expired products
DELETE FROM products 
WHERE expiry_date < CURDATE();

Delete with Multiple Conditions

-- Delete inactive users who haven't logged in for a year
DELETE FROM users 
WHERE active = 0 
  AND last_login < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- Delete old log entries
DELETE FROM system_logs 
WHERE log_level = 'DEBUG' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

DELETE with LIMIT

Limit the number of rows deleted (useful for batch operations):

-- Delete oldest 1000 records
DELETE FROM audit_trail 
ORDER BY created_at ASC
LIMIT 1000;

-- Delete duplicate records (keeping the latest)
DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE 
    t1.id < t2.id 
    AND t1.email = t2.email
LIMIT 100;

Delete Using Subqueries

-- Delete orders that have been cancelled for over 30 days
DELETE FROM orders 
WHERE order_id IN (
    SELECT order_id 
    FROM order_status 
    WHERE status = 'Cancelled' 
      AND status_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);

-- Delete products with no sales in the last year
DELETE FROM products 
WHERE product_id NOT IN (
    SELECT DISTINCT product_id 
    FROM order_items 
    WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
);

DELETE with JOIN

-- Delete employees who left the company
DELETE e
FROM employees e
JOIN termination_records t ON e.employee_id = t.employee_id
WHERE t.termination_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY);

-- Delete orphaned records
DELETE c
FROM comments c
LEFT JOIN posts p ON c.post_id = p.post_id
WHERE p.post_id IS NULL;

TRUNCATE TABLE

TRUNCATE removes all records from a table, but unlike DELETE, it cannot be rolled back and does not fire triggers. It's faster for large tables.

-- Remove all data from a table
TRUNCATE TABLE temp_data;

-- TRUNCATE resets auto-increment values
TRUNCATE TABLE session_logs;

Practical Examples

-- Implement data retention policy (delete old records in batches)
SET @batch_size = 1000;
SET @deleted_rows = 1;

WHILE @deleted_rows > 0 DO
    START TRANSACTION;
    
    DELETE FROM user_activity_logs 
    WHERE activity_date < DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
    ORDER BY activity_date ASC
    LIMIT @batch_size;
    
    SET @deleted_rows = ROW_COUNT();
    
    -- Archive before deleting (pseudo-code)
    -- INSERT INTO user_activity_logs_archive 
    -- SELECT * FROM user_activity_logs 
    -- WHERE activity_date < DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
    -- ORDER BY activity_date ASC
    -- LIMIT @batch_size;
    
    COMMIT;
    
    -- Pause between batches
    DO SLEEP(1);
END WHILE;

-- GDPR right to be forgotten implementation
START TRANSACTION;

-- First, archive the data for legal compliance
INSERT INTO gdpr_deletion_log (
    user_id, 
    email, 
    deletion_reason, 
    deleted_at,
    archived_data
)
SELECT 
    u.user_id,
    u.email,
    'GDPR Right to Be Forgotten',
    NOW(),
    JSON_OBJECT(
        'user_data', (SELECT JSON_OBJECT('name', u.name, 'email', u.email)),
        'orders', (SELECT JSON_ARRAYAGG(JSON_OBJECT('order_id', o.order_id, 'amount', o.amount))
                   FROM orders o WHERE o.user_id = u.user_id),
        'activities', (SELECT JSON_ARRAYAGG(a.activity_type) 
                      FROM user_activities a WHERE a.user_id = u.user_id)
    )
FROM users u
WHERE u.user_id = 123456
  AND u.gdpr_delete_request = 1
  AND u.gdpr_deleted = 0;

-- Then delete from main tables (in reverse order of foreign key constraints)
DELETE FROM user_sessions WHERE user_id = 123456;
DELETE FROM user_preferences WHERE user_id = 123456;
DELETE FROM user_activities WHERE user_id = 123456;
DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE user_id = 123456);
DELETE FROM orders WHERE user_id = 123456;
DELETE FROM addresses WHERE user_id = 123456;
DELETE FROM users WHERE user_id = 123456;

-- Mark as processed
UPDATE gdpr_requests 
SET processed = 1, processed_at = NOW()
WHERE user_id = 123456;

COMMIT;

-- Instead of hard delete, implement soft delete
-- Add these columns to your table:
-- ALTER TABLE users ADD COLUMN deleted BOOLEAN DEFAULT 0;
-- ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
-- ALTER TABLE users ADD COLUMN deleted_by INT NULL;

-- Soft delete a user
UPDATE users 
SET 
    deleted = 1,
    deleted_at = NOW(),
    deleted_by = @current_user_id,
    active = 0,
    email = CONCAT('deleted_', UNIX_TIMESTAMP(), '_', email)
WHERE user_id = 123456;

-- Query to get only active users
SELECT * FROM users WHERE deleted = 0;

-- Periodic cleanup of soft-deleted records (after 30 days)
DELETE FROM users 
WHERE deleted = 1 
  AND deleted_at < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Safety Procedures

1. Always backup first:

   CREATE TABLE backup_table AS SELECT * FROM original_table;

2. Use transactions:

   START TRANSACTION;
   -- Your DELETE statement here
   -- Check the results
   ROLLBACK; -- or COMMIT;

3. Test with SELECT:

   -- First, see what will be deleted
   SELECT COUNT(*) 
   FROM table_name 
   WHERE condition;
   
   -- Then run the DELETE
   DELETE FROM table_name 
   WHERE condition;

Alternative to DELETE: Archiving Strategy

Consider archiving instead of deleting for important data:

-- 1. Create archive table
CREATE TABLE orders_archive LIKE orders;

-- 2. Archive old records
INSERT INTO orders_archive 
SELECT * FROM orders 
WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

-- 3. Verify archive
SELECT COUNT(*) FROM orders_archive;

-- 4. Only then delete
DELETE FROM orders 
WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 2 YEAR);