DELETE: Removing Data from Tables

Sometimes data needs to be permanently removed—cancelled orders, closed accounts, expired records, or test data. DELETE is the SQL command that removes rows from your tables. It's one of the most dangerous commands in SQL because deleted data is often impossible to recover. In this lesson, you'll learn how to delete specific rows, use conditions safely, understand the difference between DELETE and TRUNCATE, handle foreign key constraints, and—most importantly—how to avoid accidentally deleting data you need to keep.

📚 What You'll Learn

• How to delete specific rows from tables
• How to use WHERE clause to target deletions
• The critical difference between DELETE, TRUNCATE, and DROP
• How to delete with foreign key constraints
• How to delete rows based on data from other tables
• How to use LIMIT with DELETE
• How to perform soft deletes (marking as deleted)
• How to test deletions safely before committing
• Common DELETE mistakes and how to prevent them

1. Basic DELETE Syntax

Complete Syntax

DELETE FROM table_name
WHERE condition;

2. Deleting Specific Rows

Delete by Primary Key (Safest)

-- Delete a specific customer by ID
DELETE FROM customers
WHERE customer_id = 42;

-- Only the customer with ID 42 is deleted

Delete by Unique Column

-- Delete customer by email (if email is UNIQUE)
DELETE FROM customers
WHERE email = 'user@example.com';

-- Delete product by SKU
DELETE FROM products
WHERE sku = 'PROD-001';

Safety Check Process

-- ALWAYS follow this process:

-- Step 1: SELECT to see what you're about to delete
SELECT * FROM customers
WHERE customer_id = 42;

-- Step 2: Verify it's the correct row
-- Check the output carefully!

-- Step 3: Run the DELETE
DELETE FROM customers
WHERE customer_id = 42;

-- Step 4: Verify it's gone
SELECT * FROM customers
WHERE customer_id = 42;
-- Should return no rows

3. Deleting Multiple Rows

You can delete many rows at once using WHERE conditions that match multiple records. This requires extreme caution.

Example: Delete Old Records

-- Delete orders older than 5 years
DELETE FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

-- ALWAYS check count first!
SELECT COUNT(*) FROM orders
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);
-- If this returns 50,000 when you expected 50, DON'T DELETE!

Example: Delete by Status

-- Delete cancelled orders
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2023-01-01';

-- Delete inactive users (not logged in for 2 years)
DELETE FROM users
WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
  AND account_status = 'inactive';

Example: Delete Duplicates

-- Delete duplicate emails, keeping the oldest account
DELETE c1 FROM customers c1
JOIN customers c2 ON c1.email = c2.email
WHERE c1.customer_id > c2.customer_id;

-- This keeps the customer with the lowest ID for each email

4. DELETE vs. TRUNCATE vs. DROP TABLE

Examples

-- DELETE: Remove specific rows
DELETE FROM logs WHERE log_date < '2024-01-01';

-- TRUNCATE: Empty the entire table (fast)
TRUNCATE TABLE session_logs;

-- DROP TABLE: Remove the table completely
DROP TABLE temp_data;

5. Deleting with Foreign Key Constraints

Foreign keys can prevent or cascade deletions. Understanding this is crucial for maintaining referential integrity.

Problem: Cannot Delete Parent Row

-- Try to delete a customer who has orders
DELETE FROM customers WHERE customer_id = 5;

-- ERROR: Cannot delete or update a parent row: 
-- a foreign key constraint fails
-- (orders has rows referencing customer_id = 5)

Solution 1: Delete Child Rows First

-- Delete in correct order: children first, then parent

-- Step 1: Delete order items (grandchild)
DELETE FROM order_items
WHERE order_id IN (
    SELECT order_id FROM orders WHERE customer_id = 5
);

-- Step 2: Delete orders (child)
DELETE FROM orders
WHERE customer_id = 5;

-- Step 3: Delete customer (parent)
DELETE FROM customers
WHERE customer_id = 5;

Solution 2: Use ON DELETE CASCADE

-- If foreign key was created with ON DELETE CASCADE:
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Then deleting the parent automatically deletes children
DELETE FROM customers WHERE customer_id = 5;
-- All orders for customer 5 are automatically deleted too!

Check What Will Be Deleted

-- Before deleting customer 5, check related records
SELECT 'Orders' AS table_name, COUNT(*) AS count
FROM orders WHERE customer_id = 5
UNION ALL
SELECT 'Order Items', COUNT(*)
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.customer_id = 5;

-- If CASCADE is enabled, all these will be deleted!

6. Deleting Based on Other Tables

Delete Using Subquery

-- Delete orders from inactive customers
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id 
    FROM customers 
    WHERE account_status = 'inactive'
);

-- Delete products with no sales in last year
DELETE FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id 
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
);

Delete Using JOIN

-- Delete orders from customers in a specific country
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'TestCountry';

-- Delete expired sessions for deleted users
DELETE s
FROM sessions s
LEFT JOIN users u ON s.user_id = u.user_id
WHERE u.user_id IS NULL;

7. Controlled Deletion with LIMIT

When deleting large amounts of data, use LIMIT to process in batches. This prevents locking the table for too long.

Delete in Batches

-- Delete oldest 1000 log entries at a time
DELETE FROM logs
WHERE log_date < '2023-01-01'
ORDER BY log_date ASC
LIMIT 1000;

-- Run this multiple times until no more rows are deleted
-- Check how many rows are affected after each run

Batch Delete Script Pattern

-- Pattern for safe batch deletion
SET @rows_affected = 1;

-- Keep deleting until no more rows match
WHILE @rows_affected > 0 DO
    DELETE FROM logs
    WHERE log_date < '2023-01-01'
    LIMIT 1000;
    
    SET @rows_affected = ROW_COUNT();
    
    -- Small delay to avoid overwhelming the database
    SELECT SLEEP(0.1);
END WHILE;

8. Soft Deletes (Recommended for Important Data)

Instead of permanently deleting data, mark it as deleted. This allows recovery and maintains audit trails.

Soft Delete Pattern

-- Add columns for soft delete
ALTER TABLE customers
ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE,
ADD COLUMN deleted_at TIMESTAMP NULL;

-- "Delete" by marking, not removing
UPDATE customers
SET is_deleted = TRUE,
    deleted_at = CURRENT_TIMESTAMP
WHERE customer_id = 42;

-- Query only active records
SELECT * FROM customers
WHERE is_deleted = FALSE;

-- Or using IS NULL
SELECT * FROM customers
WHERE deleted_at IS NULL;

Advantages of Soft Deletes

• ✅ Can easily restore "deleted" records
• ✅ Maintains audit trail (who deleted, when)
• ✅ Preserves referential integrity
• ✅ Allows reporting on deleted data
• ✅ No risk of cascade deleting important data

When to Use Soft Deletes

• ✓ User accounts (may want to reactivate)
• ✓ Orders (legal/financial records)
• ✓ Important business data
• ✓ When audit trails are required
• ❌ Temporary/cache data
• ❌ Logs (hard delete old logs)
• ❌ Session data

9. Safe Deletion Process (Follow Every Time)

Complete Safe Deletion Example

-- Step 1: Backup (optional but recommended for large deletes)
CREATE TABLE customers_backup_20241217 AS
SELECT * FROM customers WHERE customer_id = 42;

-- Step 2: Start transaction
START TRANSACTION;

-- Step 3: Preview what will be deleted
SELECT * FROM customers WHERE customer_id = 42;

-- Step 4: Count rows
SELECT COUNT(*) FROM customers WHERE customer_id = 42;
-- Expected: 1 row

-- Step 5: Check for dependent records
SELECT 'Orders' AS related_table, COUNT(*) AS count
FROM orders WHERE customer_id = 42
UNION ALL
SELECT 'Addresses', COUNT(*)
FROM addresses WHERE customer_id = 42;

-- Step 6: If safe, execute DELETE
DELETE FROM customers WHERE customer_id = 42;

-- Step 7: Verify it's gone
SELECT * FROM customers WHERE customer_id = 42;
-- Should return 0 rows

-- Step 8: Check affected rows
-- MySQL shows "1 row affected"
-- If this doesn't match expectations, ROLLBACK!

-- Step 9: Commit or rollback
COMMIT;  -- If correct
-- ROLLBACK;  -- If something's wrong

10. Common DELETE Mistakes and Prevention

Mistake 1: Forgetting WHERE Clause

DELETE FROM customers;
-- DISASTER! Every customer is now deleted!

DELETE FROM customers
WHERE customer_id = 42;

Mistake 2: Wrong WHERE Condition

-- Intend to delete test accounts, but this deletes EVERYONE
DELETE FROM users
WHERE email LIKE '%@test.com' OR email IS NOT NULL;
-- IS NOT NULL matches everyone!

-- Check first!
SELECT COUNT(*) FROM users WHERE email LIKE '%@test.com';

DELETE FROM users
WHERE email LIKE '%@test.com';

Mistake 3: Not Checking Foreign Keys

-- ❌ Forgetting to check for related records
DELETE FROM customers WHERE customer_id = 5;
-- ERROR: Foreign key constraint fails

-- ✅ Always check dependencies first
SELECT 
    'Orders' AS table_name,
    COUNT(*) AS dependent_records
FROM orders 
WHERE customer_id = 5
UNION ALL
SELECT 'Reviews', COUNT(*)
FROM reviews 
WHERE customer_id = 5;

-- Then delete in proper order if needed

Mistake 4: Deleting Without Backup

-- ❌ Dangerous: Delete important data without backup
DELETE FROM orders WHERE order_date < '2020-01-01';

-- ✅ Safe: Backup before deleting
-- Option 1: Export to file (mysqldump)
-- Option 2: Copy to backup table
CREATE TABLE orders_archive AS
SELECT * FROM orders WHERE order_date < '2020-01-01';

-- Now safe to delete
DELETE FROM orders WHERE order_date < '2020-01-01';

11. DELETE Best Practices

12. 🎯 Practice Exercises

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    account_status VARCHAR(20)
);

-- Task 1: Delete customer 999 (safe process)
-- Step 1: Check what we're deleting
SELECT * FROM customers WHERE customer_id = 999;

-- Step 2: Verify it's only 1 row
SELECT COUNT(*) FROM customers WHERE customer_id = 999;

-- Step 3: Delete
DELETE FROM customers WHERE customer_id = 999;

-- Step 4: Verify it's gone
SELECT * FROM customers WHERE customer_id = 999;


-- Task 2: Delete temporary email accounts
-- Step 1: Preview what will be deleted
SELECT customer_id, email FROM customers 
WHERE email LIKE '%@tempmail.com';

-- Step 2: Count them
SELECT COUNT(*) FROM customers 
WHERE email LIKE '%@tempmail.com';

-- Step 3: If count is reasonable, delete
DELETE FROM customers
WHERE email LIKE '%@tempmail.com';


-- Task 3: Delete by specific email
-- Step 1: Find the customer
SELECT * FROM customers WHERE email = 'test@example.com';

-- Step 2: Delete (email is UNIQUE, so only 1 row)
DELETE FROM customers
WHERE email = 'test@example.com';

-- Step 3: Verify
SELECT * FROM customers WHERE email = 'test@example.com';

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20)
);

CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    log_message TEXT,
    log_level VARCHAR(20),
    created_at TIMESTAMP
);

-- Task 1: Delete old cancelled orders
-- Step 1: Check what will be deleted
SELECT order_id, order_date, status
FROM orders
WHERE status = 'cancelled'
  AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);

-- Step 2: Count them
SELECT COUNT(*) FROM orders
WHERE status = 'cancelled'
  AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);

-- Step 3: Delete
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);


-- Task 2: Delete old debug logs
-- Step 1: Preview
SELECT log_id, log_level, created_at
FROM logs
WHERE log_level = 'DEBUG'
  AND created_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 90 DAY)
LIMIT 10;

-- Step 2: Count
SELECT COUNT(*) FROM logs
WHERE log_level = 'DEBUG'
  AND created_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 90 DAY);

-- Step 3: Delete in batches if many rows
DELETE FROM logs
WHERE log_level = 'DEBUG'
  AND created_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 90 DAY)
LIMIT 10000;
-- Repeat until no more rows


-- Task 3: Delete all 2020 orders
-- Step 1: Preview year range
SELECT MIN(order_date), MAX(order_date), COUNT(*)
FROM orders
WHERE YEAR(order_date) = 2020;

-- Alternative: Using date range
SELECT COUNT(*) FROM orders
WHERE order_date >= '2020-01-01' 
  AND order_date < '2021-01-01';

-- Step 2: Delete
DELETE FROM orders
WHERE order_date >= '2020-01-01' 
  AND order_date < '2021-01-01';

-- Verify
SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2020;

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE reviews (
    review_id INT PRIMARY KEY,
    book_id INT,
    rating INT,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Step 1: Check what will be deleted (audit before deletion)
SELECT 
    'Author' AS level,
    a.author_id,
    a.name,
    NULL AS title,
    NULL AS review_count
FROM authors a
WHERE a.author_id = 5

UNION ALL

SELECT 
    'Books' AS level,
    b.author_id,
    NULL,
    b.title,
    COUNT(r.review_id) AS review_count
FROM books b
LEFT JOIN reviews r ON b.book_id = r.book_id
WHERE b.author_id = 5
GROUP BY b.book_id;

-- Step 2: Count everything that will be deleted
SELECT 
    'Books' AS item_type,
    COUNT(*) AS count
FROM books
WHERE author_id = 5
UNION ALL
SELECT 
    'Reviews',
    COUNT(*)
FROM reviews r
JOIN books b ON r.book_id = b.book_id
WHERE b.author_id = 5;

-- Step 3: Start transaction for safety
START TRANSACTION;

-- Step 4: Delete in correct order (children first)
-- Delete reviews (grandchild)
DELETE r FROM reviews r
JOIN books b ON r.book_id = b.book_id
WHERE b.author_id = 5;

-- Verify reviews deleted
SELECT COUNT(*) FROM reviews r
JOIN books b ON r.book_id = b.book_id
WHERE b.author_id = 5;
-- Should be 0

-- Delete books (child)
DELETE FROM books
WHERE author_id = 5;

-- Verify books deleted
SELECT COUNT(*) FROM books WHERE author_id = 5;
-- Should be 0

-- Delete author (parent)
DELETE FROM authors
WHERE author_id = 5;

-- Verify author deleted
SELECT * FROM authors WHERE author_id = 5;
-- Should return no rows

-- Step 5: If all correct, commit
COMMIT;

-- If anything wrong, rollback
-- ROLLBACK;

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Task 1: Add soft delete columns
ALTER TABLE users
ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE,
ADD COLUMN deleted_at TIMESTAMP NULL,
ADD COLUMN deleted_by VARCHAR(100) NULL;

-- Task 2: Create soft delete procedure
DELIMITER //

CREATE PROCEDURE safe_delete_user(
    IN p_user_id INT,
    IN p_deleted_by VARCHAR(100)
)
BEGIN
    -- Check if user exists and is not already deleted
    IF EXISTS (
        SELECT 1 FROM users 
        WHERE user_id = p_user_id 
        AND is_deleted = FALSE
    ) THEN
        -- Soft delete the user
        UPDATE users
        SET is_deleted = TRUE,
            deleted_at = CURRENT_TIMESTAMP,
            deleted_by = p_deleted_by
        WHERE user_id = p_user_id;
        
        SELECT CONCAT('User ', p_user_id, ' soft deleted successfully') AS message;
    ELSE
        SELECT 'User not found or already deleted' AS message;
    END IF;
END //

DELIMITER ;

-- Usage:
CALL safe_delete_user(42, 'admin@example.com');


-- Task 3: Create view for active users only
CREATE VIEW active_users AS
SELECT user_id, username, email, created_at
FROM users
WHERE is_deleted = FALSE;

-- Usage:
SELECT * FROM active_users;


-- Task 4: Permanently delete old soft-deleted users
DELIMITER //

CREATE PROCEDURE purge_old_deleted_users()
BEGIN
    DECLARE rows_deleted INT DEFAULT 0;
    
    -- Find how many will be deleted
    SELECT COUNT(*) INTO rows_deleted
    FROM users
    WHERE is_deleted = TRUE
      AND deleted_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY);
    
    -- Log what we're about to do
    SELECT CONCAT('About to permanently delete ', rows_deleted, ' users') AS message;
    
    -- Permanently delete
    DELETE FROM users
    WHERE is_deleted = TRUE
      AND deleted_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY);
    
    -- Confirm deletion
    SELECT CONCAT('Permanently deleted ', rows_deleted, ' soft-deleted users') AS result;
END //

DELIMITER ;

-- Usage:
CALL purge_old_deleted_users();


-- Bonus: Restore a soft-deleted user
DELIMITER //

CREATE PROCEDURE restore_user(IN p_user_id INT)
BEGIN
    IF EXISTS (
        SELECT 1 FROM users 
        WHERE user_id = p_user_id 
        AND is_deleted = TRUE
    ) THEN
        UPDATE users
        SET is_deleted = FALSE,
            deleted_at = NULL,
            deleted_by = NULL
        WHERE user_id = p_user_id;
        
        SELECT CONCAT('User ', p_user_id, ' restored successfully') AS message;
    ELSE
        SELECT 'User not found or not deleted' AS message;
    END IF;
END //

DELIMITER ;

-- Usage:
CALL restore_user(42);

-- Soft delete a user
CALL safe_delete_user(42, 'admin@example.com');

-- Query active users (deleted users hidden)
SELECT * FROM active_users;

-- Restore accidentally deleted user
CALL restore_user(42);

-- Purge old deleted users (run monthly)
CALL purge_old_deleted_users();

📝 Key Takeaways

• DELETE FROM permanently removes rows from tables
• ALWAYS use WHERE clause unless deleting all rows intentionally
• Test with SELECT first - never run DELETE blindly
• Check row count before deleting
• DELETE vs TRUNCATE vs DROP - know the differences
• Delete children before parents with foreign keys
• Use transactions for safe deletion (ROLLBACK if wrong)
• Use LIMIT for batch deletion of large datasets
• Soft deletes are safer for important business data
• Backup before major deletions - you can't undo DELETE
• Check foreign key constraints before deleting
• Use JOIN or subqueries to delete based on other tables
• ON DELETE CASCADE automatically deletes children
• Consider audit trails (who deleted, when, why)
• Never delete production data without testing first!