Using WHERE when updating and deleting data

Imagine you’re a database admin and you need to change or delete some records. But what if you accidentally update or delete everything? Using the WHERE clause is your "insurance policy" against catastrophic mistakes. It lets you specify exactly which rows should be affected by your queries.

For example:

UPDATE students
SET status = 'Graduated';

This query will update absolutely every row in the students table. And imagine that instead of congratulating everyone on graduating, you accidentally set status = 'Expelled' for all. Scary, right? To avoid this, we always use WHERE to specify conditions that define which rows should be updated or deleted.

WHERE clause syntax

WHERE is used to filter the rows that the operator applies to. You can use it with UPDATE or DELETE. The syntax looks like this:

For UPDATE:

UPDATE table
SET column1 = value1, column2 = value2
WHERE condition;

For DELETE:

DELETE FROM table
WHERE condition;

If you skip WHERE, the changes will affect every row in the table. So always try to set conditions to avoid changing data you didn’t mean to touch.

Using comparison operators

To build conditions in WHERE, you use comparison operators. Let’s check out the main ones:

Example: update student status

If you want to mark all students older than 21 as "Graduated", do it like this:

UPDATE students
SET status = 'Graduated'
WHERE age > 21;

Example: delete all students younger than 18

If someone accidentally added underage students, you can delete them:

DELETE FROM students
WHERE age < 18;

Complex conditions with logical operators

Often you need to filter rows by several criteria. For example, to update only those students who are both older than 18 and studying in the programming course. For this, you use logical operators: AND, OR, and NOT.

Logical operators:

Example: update only programming students older than 18

UPDATE students
SET status = 'Advanced'
WHERE age > 18 AND course = 'Programming';

Example: delete all students younger than 18 or those who are in the "Singing" course

DELETE FROM students
WHERE age < 18 OR course = 'Singing';

How to avoid catastrophic mistakes

Always double-check your condition before running it!

If you write a query without WHERE, PostgreSQL will happily execute it for every row in the table. Here’s what happens:

DELETE FROM students;
-- Oops! All records from the students table are deleted!

To avoid this, add WHERE:

DELETE FROM students
WHERE student_id = 123;

Tip: if you’re not sure what effect your condition will have, start with a SELECT statement. For example:

SELECT * FROM students
WHERE student_id = 123;

Once you’re sure the selection is right, run UPDATE or DELETE.

Practical examples

Example 1: Updating student statuses

Let’s say we have a students table:

We want to mark all programming students older than 18 as "Intermediate":

UPDATE students
SET status = 'Intermediate'
WHERE age > 18 AND course = 'Programming';

Result:

Example 2: Deleting expelled students

Now let’s say we have students with courses that need to be deleted. For example, students who were in the "History of Arts" course. Table:

Let’s delete students from the "History of Arts" course:

DELETE FROM students
WHERE course = 'History of Arts';

Example 3: Updating without conditions (and why it’s bad)

If you write:

UPDATE students
SET status = 'Graduate';

EVERY student will become a graduate. That’s why it’s highly recommended to always use WHERE!

Let’s say our table originally looked like this:

After running UPDATE without WHERE:

😬 See? All statuses got replaced. This mistake can be irreversible, especially if you don’t have a backup. So WHERE is your buddy!

Gotchas and pitfalls

Even though the WHERE clause is super useful, newbies often make mistakes:

• No condition: forget WHERE and all rows get affected.
• Wrong condition: if you write, for example, age = 19 instead of age < 19, you might hit the wrong data.
• Complex conditions: wrong priorities (AND and OR) can lead to unexpected results. Use parentheses for clarity:

  DELETE FROM students
  WHERE (age > 18 AND course = 'Programming') OR course = 'Mathematics';
  

Try to write queries so they’re as clear as possible for you and your teammates.

Now you know how to use WHERE to update and delete data safely and reliably. This tool is your best friend when working with databases. And remember: it’s better to double-check your condition than to restore lost data later!