Deleting Data Using DELETE

Deleting isn’t just a function, it’s an art! It’s super important to delete exactly what you need and not touch anything extra (accidentally deleted data can haunt you in your nightmares for a long time). Today, we’ll learn how to do it carefully and by the book!

Deleting data means removing one or more rows from a table that no longer fit your business logic or system requirements. For example, you might need to delete records about students who graduated or, um, seriously broke university rules. Or maybe you just want to clean up a table to keep only the relevant info.

The delete operation is used in these scenarios:

• Deleting data about users who are no longer active.
• Cleaning up tables from temporary records.
• Deleting duplicate data.
• Deleting outdated records according to some business logic.

Syntax of the DELETE Command

To delete data, we use the DELETE command. Here’s the basic syntax:

DELETE FROM table
WHERE condition;

Main parts:

• DELETE FROM — the key phrase used to say you want to delete rows from a table.
• table — the name of the table you want to delete data from.
• WHERE condition — the filter for which rows you want to delete. Here you can use any comparison operators, logical operators (AND, OR, NOT), and even subqueries.

Example: imagine we have a students table, and we want to delete the student with ID = 5:

DELETE FROM students
WHERE id = 5;

This query will delete the row from the students table where the student’s ID (id column) is 5.

Important: if you forget to add the WHERE condition, PostgreSQL will delete all rows from the table. That kind of mistake can be a total disaster, especially if we’re talking about a production system.

Deleting All Rows from a Table

If you need to delete all rows from a table, you can just write the query without a WHERE condition. For example:

DELETE FROM students;

This query will delete all records from the students table. But the table structure will stay — it won’t be deleted, it’ll just be empty.

Alternative: TRUNCATE

To delete all rows from a table, you can also use the TRUNCATE command. It works faster than DELETE because it doesn’t log each row deletion in the transaction log. Example:

TRUNCATE TABLE students;

Differences Between DELETE and TRUNCATE:

• DELETE logs each deletion in the transaction log, so you can roll them back if needed.
• TRUNCATE doesn’t support conditions (WHERE) and doesn’t log deletions row by row, which makes it faster but less flexible.

If you just want to clear out a table, TRUNCATE is a great choice. But if you need to delete only part of the data or want to be able to roll back changes, DELETE is better.

Deleting Data with Complex Conditions

Delete conditions in DELETE can be not just simple, but also complex, including logical operators (AND, OR, NOT) or even subqueries. Let’s check out an example.

Example 1: Deleting Multiple Records Let’s delete all students whose age is over 30 and who haven’t attended classes for the last 3 months:

DELETE FROM students
WHERE age > 30 AND last_attendance_date < (CURRENT_DATE - INTERVAL '3 months');

This query will delete only those students who match both conditions at the same time.

Example 2: Deleting Using a Subquery

Let’s say we have a failed_students table that stores the IDs of students who got kicked out for academic failure. We need to delete them from the main students table:

DELETE FROM students
WHERE id IN (SELECT student_id FROM failed_students);

Here, the subquery returns all student IDs from the failed_students table, and the DELETE command deletes the matching rows from the students table.

Pro Tip

Before you delete anything, write a SELECT that shows the data you want to delete. Then just swap out the SELECT part for DELETE, and it’ll delete the same rows you just saw.

Practical Example

Let’s say a student with ID = 7 decided to transfer to another university. Let’s delete all their data from our table:

DELETE FROM students
WHERE id = 7;

Check the table after running the command to make sure the record is really gone:

SELECT * FROM students WHERE id = 7;

If there are no records with ID = 7, the delete operation worked!

Warnings and Common Mistakes

Forgotten WHERE Section

One of the most common mistakes with DELETE is forgetting the WHERE section. This leads to deleting all rows from the table, which can be a real disaster. Always double-check your queries before running them.

Example of a forgetful moment:

DELETE FROM students;
-- Oops! Deleted all students.

To avoid mistakes like this, it’s a good idea to always test your WHERE condition with a SELECT command before running DELETE. For example:

SELECT * FROM students WHERE id = 5;

If the output looks good, then go ahead and run DELETE.

Deleting Related Data

If a table has foreign keys (FOREIGN KEY), you might get an error when deleting data. This happens if the row you’re deleting is linked to other records. For example, if a student is enrolled in a course in the enrollments table, PostgreSQL won’t let you delete them from the students table.

To fix this, you can:

1. Use cascading delete ON DELETE CASCADE when creating the foreign key.
2. Delete related records manually before deleting the main record.

Example of manually deleting related data:

-- first, delete student 5’s data from the enrollments table
DELETE FROM enrollments
WHERE student_id = 5;

-- then delete student 5 themselves
DELETE FROM students
WHERE id = 5;

Deleting with Transactions

For super important delete operations, it’s a good idea to use transactions. This lets you roll back changes if something goes wrong.

Example of deleting in a transaction:

BEGIN;

DELETE FROM students
WHERE id = 42;

-- Check the result
SELECT * FROM students WHERE id = 42;

-- If everything’s good, commit the changes
COMMIT;

-- If not, roll back
-- ROLLBACK;

More about transactions in the next lectures :P