DELETE - Removing Data

Introduction

Sometimes data needs to be removed. Spam accounts, cancelled orders, expired sessions—the DELETE statement removes rows from your database.

CRITICAL WARNING: DELETE is permanent and cannot be easily undone. Always use WHERE and test carefully!

The DELETE Statement

Basic Syntax

DELETE FROM table_name
WHERE condition;

Your First DELETE

-- Remove a test user
DELETE FROM users
WHERE name = 'Test User';

Result:

DELETE 1

One row was deleted.

Verify the Deletion

SELECT * FROM users WHERE name = 'Test User';

Output:

(0 rows)

The user is gone!

The WHERE Clause is CRITICAL

Without WHERE - Deletes ALL Rows

-- ⚠️⚠️⚠️ EXTREMELY DANGEROUS!
DELETE FROM users;

Result: EVERY row in the table is deleted! The table still exists, but it's completely empty.

SELECT COUNT(*) FROM users;

Output:

 count
-------
     0

ALWAYS use WHERE unless you intentionally want to delete everything!

Best Practice: Test with SELECT First

-- 1. Test what will be deleted
SELECT * FROM users WHERE age < 18;

-- 2. If results look right, DELETE
DELETE FROM users WHERE age < 18;

Deleting Multiple Rows

-- Delete all users from a specific domain
DELETE FROM users
WHERE email LIKE '%@spam.com';

Result:

DELETE 5

Five rows were deleted.

Deleting with Multiple Conditions

-- Delete inactive users over 65
DELETE FROM users
WHERE age > 65 AND email IS NULL;

RETURNING Clause

See What Was Deleted

DELETE FROM users
WHERE age > 80
RETURNING *;

Output:

 id |     name      |       email        | age
----+---------------+--------------------+-----
 15 | Old User 1    | old1@example.com   |  85
 16 | Old User 2    | old2@example.com   |  90

Shows deleted rows before they're gone forever.

Return Specific Columns

DELETE FROM users
WHERE id = 20
RETURNING id, name;

Useful for logging what was deleted.

DELETE vs TRUNCATE vs DROP

DELETE

DELETE FROM users WHERE age < 18;

• Removes specific rows
• Can use WHERE clause
• Slower for large deletes
• Can be rolled back (in transactions)

TRUNCATE

TRUNCATE TABLE users;

• Removes ALL rows
• Faster than DELETE (doesn't log each row)
• Resets auto-increment counters
• Cannot use WHERE
• Cannot be easily rolled back

DROP

DROP TABLE users;

• Removes the entire table (structure + data)
• Cannot be undone
• Use only when deleting the table permanently

Comparison:

Deleting with Subqueries

-- Delete users who haven't made any orders
DELETE FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

Foreign Key Constraints

ON DELETE CASCADE

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);

-- Insert data
INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO orders (customer_id) VALUES (1), (1), (1);

-- Delete customer
DELETE FROM customers WHERE id = 1;

-- All related orders are automatically deleted!
SELECT COUNT(*) FROM orders;  -- 0 rows

ON DELETE RESTRICT (Default)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT
);

-- Cannot delete customer with existing orders
DELETE FROM customers WHERE id = 1;

Error:

ERROR: update or delete on table "customers" violates foreign key constraint

Must delete orders first, then customer.

ON DELETE SET NULL

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
);

DELETE FROM customers WHERE id = 1;

-- Orders remain, but customer_id is set to NULL
SELECT * FROM orders;

Soft Deletes (Recommended Pattern)

Instead of actually deleting, mark rows as deleted:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- "Delete" a user (soft delete)
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 5;

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

-- Query deleted users
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Benefits:

• Data recovery possible
• Audit trail
• No foreign key issues
• Can "undelete" easily

To undelete:

UPDATE users SET deleted_at = NULL WHERE id = 5;

Safety Tips

1. Use Transactions

BEGIN;

DELETE FROM users WHERE age < 18;

-- Check what's left
SELECT COUNT(*) FROM users;

-- If good:
COMMIT;

-- If wrong:
ROLLBACK;  -- Undo the delete!

2. Backup Before Mass Deletes

-- Create backup
CREATE TABLE users_backup AS SELECT * FROM users;

-- Do deletes
DELETE FROM users WHERE inactive = true;

-- If something went wrong, restore:
-- TRUNCATE users;
-- INSERT INTO users SELECT * FROM users_backup;

3. Delete in Batches

-- Instead of deleting 1 million rows at once:
-- DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete in batches of 1000:
DELETE FROM users
WHERE id IN (
    SELECT id FROM users
    WHERE created_at < '2020-01-01'
    LIMIT 1000
);
-- Repeat until all deleted

4. Use WHERE with Primary Keys

-- Safe: Deletes exactly one row
DELETE FROM users WHERE id = 5;

-- Risky: Might delete multiple rows
DELETE FROM users WHERE name = 'John';

Common Patterns

Pattern 1: Delete Old Records

-- Delete logs older than 30 days
DELETE FROM logs
WHERE created_at < CURRENT_DATE - INTERVAL '30 days';

Pattern 2: Delete Duplicates

-- Delete duplicate emails, keeping the oldest
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

Pattern 3: Clean Up Orphaned Records

-- Delete orders with no matching customer
DELETE FROM orders
WHERE customer_id NOT IN (
    SELECT id FROM customers
);

Common Mistakes

Mistake 1: Forgetting WHERE

-- ❌ Deletes EVERYTHING
DELETE FROM users;

-- ✅ Deletes specific rows
DELETE FROM users WHERE age < 18;

Mistake 2: Wrong WHERE Condition

-- Intended to delete user 5, but wrong operator
DELETE FROM users WHERE id = '5';  -- Works, but be careful with type casting

-- ✅ Correct
DELETE FROM users WHERE id = 5;

Mistake 3: Forgetting Foreign Keys

-- ❌ Error if orders reference this customer
DELETE FROM customers WHERE id = 1;

-- ✅ Delete orders first
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;

Mistake 4: Not Using Transactions

-- ❌ No way to undo
DELETE FROM users WHERE active = false;

-- ✅ Can rollback if wrong
BEGIN;
DELETE FROM users WHERE active = false;
-- Review changes...
COMMIT; -- or ROLLBACK

Performance Considerations

Indexes Speed Up Deletes

CREATE INDEX idx_users_created_at ON users(created_at);

-- Faster delete with indexed column
DELETE FROM users WHERE created_at < '2020-01-01';

Large Deletes Lock Tables

-- Deleting millions of rows can lock the table for a long time
-- Use batching:
DO $$
DECLARE
    deleted INTEGER;
BEGIN
    LOOP
        DELETE FROM users
        WHERE id IN (
            SELECT id FROM users WHERE inactive = true LIMIT 10000
        );
        GET DIAGNOSTICS deleted = ROW_COUNT;
        EXIT WHEN deleted = 0;
        COMMIT;
    END LOOP;
END $$;

Practice Exercises

Exercise 1

Delete the user with name 'Test Account'.

DELETE FROM users WHERE name = 'Test Account';

Exercise 2

Delete all users under age 18.

DELETE FROM users WHERE age < 18;

Exercise 3

Delete users with NULL email and return their names.

DELETE FROM users
WHERE email IS NULL
RETURNING name;

Exercise 4

Soft delete a user (set deleted_at to current timestamp).

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 10;

Key Takeaways

• ✅ DELETE FROM removes rows from tables
• ✅ WHERE determines which rows to delete
• ✅ Without WHERE, all rows are deleted
• ✅ RETURNING shows deleted data
• ✅ Test with SELECT before DELETE
• ✅ Use transactions for safety
• ✅ Consider soft deletes for recoverability
• ✅ TRUNCATE for fast deletion of all rows

Next Steps

You now know INSERT, UPDATE, and DELETE—the three commands for modifying data. In the next lesson, we'll explore Data Types in SQL—understanding how to choose the right type for each column.

Be careful with DELETE, and keep learning!