SQL DML

SQL DELETE

Remove records from a database safely using DELETE with WHERE clauses.

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

ALWAYS use WHERE with DELETE!

If you omit the WHERE clause, ALL records in the table will be deleted! This is one of the most common and catastrophic mistakes in database management.

Soft Delete Pattern

Instead of actually deleting records, many applications use a "soft delete" pattern: adding a deleted_at column and filtering it out in queries. This preserves data for auditing and recovery.

TRUNCATE vs DELETE

DELETE removes specific rows (with WHERE) or all rows
TRUNCATE removes all rows much faster, resets auto-increment
TRUNCATE cannot be rolled back in many databases

Example

sql

-- Delete a specific record
DELETE FROM users WHERE id = 42;

-- Delete with multiple conditions
DELETE FROM sessions
WHERE user_id = 42
  AND created_at < NOW() - INTERVAL '30 days';

-- Delete based on subquery
DELETE FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE banned = true
);

-- Delete with RETURNING (PostgreSQL)
DELETE FROM temp_tokens
WHERE expires_at < NOW()
RETURNING id, user_id;

-- SOFT DELETE pattern (recommended!)
-- Instead of DELETE, mark as deleted:
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;

UPDATE posts
SET deleted_at = NOW()
WHERE id = 42;

-- Query excluding deleted
SELECT * FROM posts WHERE deleted_at IS NULL;

-- TRUNCATE (removes ALL rows, fast)
TRUNCATE TABLE sessions;  -- No WHERE clause!

-- Delete all rows with DELETE
DELETE FROM temp_data;  -- Slower but transactional

Want to run this code interactively?

Try in Compiler