DELETE Statement

Remove data from tables using the DELETE statement.

12 min read
Beginner

Introduction to DELETE

Sometimes you need to remove data from your database: a user closes their account, an old record is no longer needed, or incorrect data was inserted by mistake.

The DELETE statement permanently removes rows from a table. Unlike UPDATE which modifies data, DELETE completely removes rows.

A SQL command that permanently removes rows from a table based on a condition. Once deleted, the data is gone (unless you have backups or transactions).

Basic Syntax:

DELETE FROM table_name
WHERE condition;

Let's work with a customers table:

customers
id
name
email
status
signup_date
1Alice Johnsonalice@email.comactive2023-01-15
2Bob Smithbob@email.cominactive2022-06-20
3Charlie Davischarlie@email.comactive2023-03-10
4Diana Wilsondiana@email.cominactive2021-08-01
5Eve Martinezeve@email.combanned2022-11-12
6Frank Brownfrank@email.comactive2023-05-28

⚠️ CRITICAL WARNING:

  • DELETE is permanent - deleted data cannot be recovered (without backups)
  • Always use a WHERE clause! Without it, all rows will be deleted
  • Test with SELECT first to see what will be deleted

Deleting a Single Row

Let's delete a specific customer by their ID:

sql
-- Delete customer with id = 5
DELETE FROM customers
WHERE id = 5;

Let's verify the deletion:

sql
SELECT * FROM customers;

Eve Martinez (id = 5) is gone! Notice that:

  • The row is completely removed
  • The IDs of other rows don't change (Frank is still id = 6)
  • There's now a "gap" in the ID sequence

Deleting Multiple Rows

You can delete multiple rows at once using a WHERE condition that matches multiple records:

sql
-- Delete all inactive customers
DELETE FROM customers
WHERE status = 'inactive';
sql
SELECT * FROM customers;

Both Bob and Diana (who had status = 'inactive') were deleted. Only active customers remain.

Using Complex WHERE Conditions

You can use any WHERE condition you've learned - comparisons, dates, multiple conditions, etc.

Delete based on dates:

sql
-- Delete old registrations (before 2023)
DELETE FROM customers
WHERE signup_date < '2023-01-01';

In our case, we already deleted the old customers, so 0 rows were affected.

Delete with multiple conditions:

sql
-- Delete inactive customers who signed up before 2023
DELETE FROM customers
WHERE status = 'inactive'
  AND signup_date < '2023-01-01';

Delete with NOT:

sql
-- Delete everyone who is NOT active
DELETE FROM customers
WHERE status != 'active';

The Danger of Missing WHERE Clause

Just like with UPDATE, forgetting the WHERE clause is catastrophic with DELETE:

⚠️ EXTREMELY DANGEROUS - DON'T DO THIS:

-- OOPS! Forgot WHERE clause
DELETE FROM customers;

Result: Every single customer in the table is permanently deleted!

Query OK, 3 rows deleted (0.01 sec)

Your entire customers table is now empty. All data is gone forever (unless you have backups).

How to prevent this disaster:

  1. Always write WHERE first before DELETE
  2. Test with SELECT - run SELECT with your WHERE clause to see what would be deleted
  3. Use transactions (advanced topic) to allow rollback
  4. Double and triple-check before pressing Enter
  5. Have backups - mistakes happen!

Best Practice - The Two-Step Safety Check:

-- Step 1: Preview what will be deleted
SELECT * FROM customers WHERE id = 5;

-- Step 2: If that looks correct, change SELECT to DELETE
DELETE FROM customers WHERE id = 5;

This is the single most important habit to develop with DELETE!

DELETE vs TRUNCATE vs DROP

There are three ways to remove data. Understanding the difference is crucial:

Comparison: DELETE vs TRUNCATE vs DROP
Command
What it does
Can use WHERE?
Reversible?
Speed
DELETERemoves specific rows✅ YesWith transactionsSlow (row-by-row)
TRUNCATERemoves ALL rows❌ No❌ NoVery fast
DROPDeletes the entire table❌ No❌ NoInstant

DELETE FROM - Removes rows (what we've been learning):

sql
DELETE FROM customers WHERE id = 1;

TRUNCATE - Removes all rows, keeps table structure:

sql
TRUNCATE TABLE customers;

After TRUNCATE:

  • Table still exists
  • All columns still defined
  • All rows are gone
  • Much faster than DELETE FROM customers;
  • Cannot be rolled back!

DROP TABLE - Completely destroys the table:

sql
DROP TABLE customers;

After DROP:

  • Table no longer exists
  • All data is gone
  • All structure (columns, constraints) is gone
  • Everything is permanently deleted!

When to use each:

  • DELETE FROM with WHERE: Remove specific rows (normal usage)
  • TRUNCATE: Quickly empty an entire table (testing, data refreshes)
  • DROP TABLE: Permanently remove a table you no longer need

Most of the time, you'll use DELETE FROM with a WHERE clause!

Verifying Before Deleting

Let's create a new example with test data:

orders (Before Cleanup)
id
customer_id
status
order_date
1101completed2024-01-15
2102cancelled2024-01-16
3103completed2024-01-17
4104cancelled2024-01-18
5105pending2024-02-01

Goal: Delete all cancelled orders

Step 1: Preview with SELECT

sql
-- See what will be deleted
SELECT * FROM orders WHERE status = 'cancelled';

Good! These are the 2 cancelled orders we want to remove.

Step 2: Execute DELETE

sql
-- Same WHERE clause, but now DELETE
DELETE FROM orders WHERE status = 'cancelled';

Step 3: Verify the result

sql
SELECT * FROM orders;

Perfect! Only the cancelled orders were removed. The completed and pending orders remain.

Practical Examples

Example 1: Remove test data

sql
-- Delete test accounts (email contains 'test')
DELETE FROM users
WHERE email LIKE '%test%';

Example 2: Data retention policy

sql
-- Delete logs older than 90 days
DELETE FROM activity_logs
WHERE created_at < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);

Example 3: Remove duplicates (advanced)

sql
-- Keep only the most recent duplicate entry
DELETE FROM products
WHERE id NOT IN (
    SELECT MAX(id)
    FROM products
    GROUP BY product_name
);

Checking DELETE Results

SQL provides feedback after DELETE operations:

DELETE Feedback Messages
Message
Meaning
`Query OK, 5 rows deleted`5 rows were successfully deleted
`Query OK, 0 rows deleted`No rows matched the WHERE condition
`Query OK, 0 rows affected`Alternative way to say "0 rows deleted"

If 0 rows were deleted:

This means no rows matched your WHERE condition. This could be:

  • Expected: The data you wanted to delete doesn't exist (maybe already deleted)
  • Unexpected: Your WHERE condition might have a typo or be incorrect

Always investigate when you expect deletions but get 0 rows affected!

sql
-- Trying to delete a non-existent customer
DELETE FROM customers WHERE id = 9999;

Common Mistakes to Avoid

Don't make these mistakes:

  1. Forgetting WHERE clause: Deletes all rows! ❌

    • ALWAYS include WHERE unless you truly want to delete everything

  2. Wrong WHERE condition: Deletes the wrong rows ❌

    • Test with SELECT first: SELECT * FROM customers WHERE id = 5;

  3. Confusing DELETE with DROP: ❌

    • DELETE removes rows, DROP removes the entire table

  4. Not having backups: One mistake = permanent data loss ❌

    • Always maintain database backups

  5. Using DELETE instead of TRUNCATE: Slow for large deletions ❌

    • If deleting all rows, use TRUNCATE TABLE table_name; (much faster)

  6. Not checking what will be deleted: Surprises are bad! ❌

    • Always run SELECT with the same WHERE clause first

  7. Forgetting about foreign key constraints: ❌

    • You can't delete a row if other tables reference it (we'll learn this later)

Safety Checklist

Before running any DELETE statement, go through this checklist:

DELETE Safety Checklist
Step
Action
Example
☐Write SELECT version`SELECT * FROM customers WHERE id = 5;`
☐Verify correct rows shownCheck the output carefully
☐Count rows to be deletedCheck the row count
☐Write DELETE version`DELETE FROM customers WHERE id = 5;`
☐Double-check WHERE clauseIs it exactly right?
☐Execute DELETERun the statement
☐Check rows affectedDoes the count match expectations?
☐Verify with SELECT`SELECT * FROM customers;`

Key Takeaways

What you learned:

✅ DELETE FROM permanently removes rows from a table ✅ WHERE specifies which rows to delete (CRITICAL!) ✅ Forgetting WHERE deletes all rows - extremely dangerous! ✅ Always test with SELECT first to preview what will be deleted ✅ DELETE is permanent - no undo without backups/transactions ✅ DELETE removes rows, TRUNCATE empties table, DROP destroys table ✅ Check feedback message to confirm rows were deleted ✅ Use the two-step safety check: SELECT first, then DELETE

Coming up next: We'll learn about comparison and logical operators (AND, OR, NOT) to create more sophisticated WHERE conditions for filtering, updating, and deleting data!

Practice Exercise: Try these DELETE statements (on test data!):

  1. Delete a specific customer by ID
  2. Delete all customers with 'inactive' status
  3. Delete customers who signed up before a specific date
  4. Practice the two-step safety check (SELECT then DELETE)

Remember: DELETE is powerful and permanent. With great power comes great responsibility!

🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.