Ad – 728×90
✏️ Data Modification

SQL DELETE – Removing Rows from Tables

DELETE removes rows from a table. Like UPDATE, it is irreversible without a transaction and extremely dangerous without a WHERE clause. Production code should never run a DELETE without a WHERE — and should always test with SELECT first.

⏱️ 15 min read 🎯 Beginner 📅 Updated 2026

Basic DELETE Syntax

SQL
DELETE FROM table_name
WHERE  condition;   -- ⚠️ without WHERE, ALL rows are deleted!
SQL
-- Delete a specific order
DELETE FROM orders
WHERE id = 1042;

-- Delete all cancelled orders older than 1 year
DELETE FROM orders
WHERE status      = 'cancelled'
  AND created_at  < NOW() - INTERVAL '1 year';

-- Delete duplicate rows keeping the lowest id
DELETE FROM contacts
WHERE id NOT IN (
    SELECT MIN(id)
    FROM contacts
    GROUP BY email       -- keep one row per email
);
⚠️
DELETE without WHERE deletes ALL rows

There is no confirmation prompt. The rows are gone immediately (unless wrapped in a transaction). Always run the matching SELECT first: replace DELETE FROM orders with SELECT COUNT(*) FROM orders to see how many rows would be deleted.

DELETE with Subquery

Use a subquery to target rows based on data in another table:

SQL
-- Delete users who have been inactive for over 2 years
DELETE FROM users
WHERE id IN (
    SELECT user_id
    FROM user_activity
    GROUP BY user_id
    HAVING MAX(last_seen) < NOW() - INTERVAL '2 years'
);

-- Delete orders for users who have been banned
DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM users WHERE status = 'banned'
);

-- PostgreSQL: DELETE with USING (more readable than subquery)
DELETE FROM orders o
USING users u
WHERE o.customer_id = u.id
  AND u.status = 'banned';
Ad – 336×280

TRUNCATE vs DELETE

TRUNCATE removes all rows from a table, but behaves differently from a DELETE without WHERE:

FeatureDELETE (no WHERE)TRUNCATE
Removes all rows?YesYes
SpeedSlow (row-by-row log)Very fast (deallocates pages)
Transaction safe?Yes (can ROLLBACK)Depends on DB (not in MySQL)
Resets auto-increment?NoYes (in most databases)
Fires triggers?YesNo (in most databases)
WHERE clause?YesNo
Respects foreign keys?YesVaries (error if referenced)
SQL
-- TRUNCATE: fastest way to empty a table (no row-by-row logging)
TRUNCATE TABLE session_logs;

-- TRUNCATE with CASCADE (PostgreSQL): also truncates referencing tables
TRUNCATE TABLE users CASCADE;

-- DELETE: slower but transactional and supports WHERE
DELETE FROM session_logs WHERE created_at < '2024-01-01';

Soft Delete Pattern

Instead of truly deleting rows, many applications mark them as deleted. This preserves history, allows undo, and simplifies auditing:

SQL
-- Add a deleted_at column to the table
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- Soft delete: set the timestamp instead of deleting the row
UPDATE users
SET    deleted_at = NOW()
WHERE  id = 42;

-- Query: always filter out soft-deleted rows
SELECT id, name, email
FROM   users
WHERE  deleted_at IS NULL;   -- active users only

-- Restore a soft-deleted row
UPDATE users
SET    deleted_at = NULL
WHERE  id = 42;
💡
Soft delete trade-offs

Benefits: auditable, recoverable, no referential integrity issues. Drawbacks: every query needs WHERE deleted_at IS NULL (partial indexes help), unique constraints may conflict (a new user with the same email as a soft-deleted user). Most ORMs and application frameworks provide built-in soft delete support.

Safety Practices

SQL
-- Safe delete workflow
BEGIN;

-- Step 1: verify which rows will be affected
SELECT id, status, created_at
FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 2: confirm the count
SELECT COUNT(*) FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 3: run the delete
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Step 4: check rows affected, commit if correct
COMMIT;
-- Or: ROLLBACK;

Summary

📋 Summary

  • DELETE FROM table WHERE condition — always include the WHERE clause.
  • Use subqueries or JOINs (PostgreSQL: USING) to delete based on data in other tables.
  • TRUNCATE is faster for emptying entire tables but resets auto-increment and may not be rollback-safe in all databases.
  • Soft delete: add a deleted_at column and UPDATE instead of DELETE — preserves history and allows recovery.
  • DANGER: DELETE without WHERE removes all rows. Always test with SELECT and use transactions.
🗄️ UPDATE Data 🗄️ Transactions 🗄️ INSERT Data 🗄️ UPSERT

FAQ

Can I DELETE rows that are referenced by foreign keys? +

Not directly — the database will raise a foreign key violation error. You must first delete or update the referencing rows. Or define the foreign key with ON DELETE CASCADE so the database automatically deletes child rows. Or use ON DELETE SET NULL to null out the foreign key column in child rows. Choose based on your data integrity requirements.

How do I delete duplicate rows keeping just one? +

The standard pattern uses a subquery to identify the row to keep (e.g., MIN(id) per group), then delete all others: DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY email). In PostgreSQL you can also use a CTE with ROW_NUMBER() to mark duplicates, then delete those with rn > 1.

Is TRUNCATE faster than DELETE for emptying large tables? +

Yes, dramatically faster. DELETE logs each row removal individually (for MVCC/WAL), so deleting 10 million rows generates 10 million log entries. TRUNCATE deallocates data pages at the storage level — it is an O(1) operation regardless of table size. For emptying whole tables use TRUNCATE; for conditional row removal use DELETE.

Ad – 728×90