DELETE Data

The DELETE statement is used to remove rows from a table. This chapter introduces various uses of the DELETE statement.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Delete Single Row

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

Delete Multiple Rows

-- Delete all inactive users
DELETE FROM users
WHERE status = 'inactive';

Delete with Subquery

-- Delete users with no orders
DELETE FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

Delete with JOIN

-- MySQL syntax
DELETE u
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Delete All Rows

-- Delete all data (keeps table structure)
DELETE FROM users;

-- Faster alternative: TRUNCATE
TRUNCATE TABLE users;

TRUNCATE vs DELETE

Feature	DELETE	TRUNCATE
Speed	Slower	Faster
WHERE clause	Yes	No
Rollback	Yes	No (in most databases)
Triggers	Fires	Doesn't fire
Auto-increment	Keeps value	Resets to 1

Practical Examples

Delete Old Records

-- Delete orders older than 1 year
DELETE FROM orders
WHERE order_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

Batch Delete

-- Delete in batches to avoid locking
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;

Delete with Transaction

START TRANSACTION;

-- Delete user
DELETE FROM users WHERE id = 1;

-- Delete related data
DELETE FROM user_profiles WHERE user_id = 1;
DELETE FROM orders WHERE user_id = 1;

COMMIT;

Cascade Delete

-- Foreign key with CASCADE
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

-- Deleting user automatically deletes their orders
DELETE FROM users WHERE id = 1;

Soft Delete

-- Instead of deleting, mark as deleted
UPDATE users
SET deleted_at = NOW(), status = 'deleted'
WHERE id = 1;

-- Query only active users
SELECT * FROM users WHERE deleted_at IS NULL;

Common Errors

Error 1: Forgetting WHERE Clause

-- DANGER: Deletes all rows!
DELETE FROM users;

-- Correct: Use WHERE clause
DELETE FROM users WHERE id = 1;

Error 2: Foreign Key Constraint

-- Error: Cannot delete due to foreign key
DELETE FROM users WHERE id = 1;
-- Error: Cannot delete or update a parent row

-- Solution 1: Delete child records first
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- Solution 2: Use CASCADE

Best Practices

1. Always use WHERE clause
2. Test with SELECT first
3. Use transactions for related deletes
4. Backup data before bulk deletes
5. Consider soft delete for important data
6. Use LIMIT for batch deletes

Performance Tips

-- Create index for WHERE columns
CREATE INDEX idx_status ON users(status);

-- Batch delete
DELETE FROM logs 
WHERE created_at < '2023-01-01' 
LIMIT 10000;

Summary

• DELETE FROM: Remove rows
• WHERE clause: Specify which rows to delete
• TRUNCATE: Fast delete all rows
• Cascade delete: Automatic deletion of related data
• Soft delete: Mark as deleted instead of removing

Next Step: Learn ALTER Tables