SQL Lesson 15: Deleting Rows

Learn to remove data from databases using DELETE statements with proper WHERE conditions and safety practices.

SQL Lesson 15: Deleting Rows

When you need to delete data from a table in the database, you can use a DELETE statement, which describes the table to act on, and the rows of the table to delete through the WHERE clause.

Delete statement with condition

DELETE FROM

mytable

WHERE

condition;

If you decide to leave out the WHERE constraint, then all rows are removed, which is a quick and easy way to clear out a table completely (if intentional).

Taking Extra Care

Like the UPDATE statement from last lesson, it's recommended that you run the constraint in aSELECT query first to ensure that you are removing the right rows. Without a proper backup or test database, it is downright easy to irrevocably remove data, so always read your DELETE statements twice and execute once.

🚨 CRITICAL DELETE Safety Rules:

ALWAYS use WHERE: Without it, ALL rows will be deleted permanently
Test with SELECT first: Verify which rows will be deleted
Use specific conditions: Target exact rows with unique identifiers
Backup before DELETE: Have recovery plans for critical data
Read twice, execute once: Double-check your DELETE statements
No UNDO: Deleted data is gone forever without backups

DELETE Best Practices:

1. Test your WHERE clause first:

SELECT * FROM movies WHERE year < 2005; -- Test first
DELETE FROM movies WHERE year < 2005; -- Then delete

2. Use specific conditions:

DELETE FROM movies WHERE id = 5; -- Specific ID
DELETE FROM movies WHERE director = "Andrew Stanton"; -- Specific director

3. Count rows before deleting:

SELECT COUNT(*) FROM movies WHERE year < 2005; -- Check count first

Common DELETE Patterns:

Date-based cleanup: DELETE FROM logs WHERE created_date < '2023-01-01';

Status-based removal: DELETE FROM orders WHERE status = 'cancelled';

Duplicate removal: DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

Conditional cleanup: DELETE FROM temp_data WHERE processed = 1;

Exercise

The database needs to be cleaned up a little bit, so try and delete a few rows in the tasks below.

Table: movies

Exercise 15 — Tasks

This database is getting too big, lets remove all movies that were released before 2005.
Andrew Stanton has also left the studio, so please remove all movies directed by him.

Solve all tasks to continue to the next lesson.

Progress0 of 2 tasks completed

Database Cleanup Required!

The movies database has grown too large and needs cleanup. Rows marked for deletion are highlighted inredin the table above. Use DELETE statements with proper WHERE conditions to remove unwanted data.

Cleanup Tasks:

Remove movies released before 2005 (outdated content)
Remove all movies by Andrew Stanton (director left studio)

⚠️ Safety Reminder: Always test your WHERE conditions with SELECT first!