Updating Data Using UPDATE

Today we're getting to another key building block of your new knowledge base—updating data. Let's see how to change existing info in PostgreSQL with the UPDATE command. Why does this matter? Well, because in real life, data isn't static. Imagine your buddy changes their phone number, or a student switches to another group—of course, you'll want to update that in your database.

When your high school crush, Svetka Sokolova, suddenly becomes Svetka Khachaturyan (hey, that's life), that means you gotta make some changes in your database. Scenarios where you need to update data pop up all the time:

• Fixing mistakes in a record.
• Updating info (moving, status change).
• Mass changes, like giving all employees a raise.

Updating data is a way to change the values of one or more columns for one or more rows in a table, without adding new records.

Syntax of the UPDATE Command

The UPDATE command in PostgreSQL has a pretty straightforward syntax. Let's check out the structure:

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

Here's what it means:

• table: the name of the table where you're making changes.
• SET column = value: this tells which column to change and what value to set.
• WHERE condition: the condition that picks which rows to update (super important, so you don't update everything by accident).

Key detail: if you forget to add WHERE (or mess it up), your changes will hit every row in the table. That can get ugly fast.

Example: Changing a Student's Name

Let's say we have a students table with columns id, name, email. We want to change the name of the student with id = 1 to "Maria Chi".

UPDATE students
SET name = 'Maria Chi'
WHERE id = 1;

This command finds the row with id = 1 and updates the name column to the new value. Easy, right?

Updating Multiple Columns

Sometimes you need to change a bunch of values in a record at once. For example, imagine a student changed not just their name, but also their email address. Here's how you'd do that:

UPDATE students
SET name = 'Otto Lin',
    email = 'otto.lin@example.com'
WHERE id = 2;

Here we're updating both name and email for the student with id = 2. PostgreSQL lets you list multiple column=value pairs, separated by commas, in the SET section.

Updating Multiple Rows

What if you need to change data for several rows at once? For example, you want to move a group of students to new streams. You can update rows that match a certain criteria. Let's say we have a group_number field, and we want to move all students from group 101 to group 202:

UPDATE students
SET group_number = 202
WHERE group_number = 101;

This command updates all rows where group_number is 101, changing it to 202.

5. The WHERE Condition: Be Careful!

Like we said earlier, WHERE is your lifeline. Without it, your query will hit every row in the table. For example, the following command will update the GROUP for all students, not just group 101. That's definitely not what you want:

UPDATE students
SET group_number = 202;

Please, always add WHERE if you don't want to accidentally nuke your data. Better safe than sorry.

Think of the UPDATE command as a command that changes table columns, not rows. Its job is to set a certain value for all cells in a column. Only having WHERE makes it so not all rows get changed.

Updating Based on Data from Another Table

Sometimes you don't just want to "type in" a value manually, but update values in table A using data from table B. This comes up a lot—especially if your data is split up: like calculations in one table, user profiles in another.

Let's say we have a students table, and we need to update the debt column using the payments table, where the current debt (due_amount) is stored. Each record in students matches a record in payments by student_id.

The syntax looks like this:

UPDATE students
SET debt = payments.due_amount
FROM payments
WHERE students.id = payments.student_id;

What's happening here:

• PostgreSQL uses FROM payments as a data source.
• The WHERE links rows between the tables.
• Only those students rows that have a match in payments get updated.

Important to know: inside this UPDATE ... FROM there's actually a hidden JOIN going on, just without the JOIN keyword.

Basically, there's a hidden JOIN under the hood. It really looks something like this:

UPDATE students
SET debt = p.due_amount
FROM payments p
    JOIN students s ON s.id = p.student_id
WHERE students.id = p.student_id;

How to See What Will Be Updated

Before you run UPDATE, it's super smart to check what exactly will change. You can do this with a SELECT written the same way:

SELECT
    students.id,
    students.name,
    students.debt AS old_debt,
    payments.due_amount AS new_debt
FROM students
    JOIN payments ON students.id = payments.student_id;

The result will show:

• the old debt value (old_debt);
• the new value coming from the payments table (new_debt).

This approach helps you double-check your logic before you overwrite your data for good.

Potential Pitfalls

• If payments has multiple records for one student, UPDATE will throw an error: more than one row returned. You need to either use aggregation (MAX, SUM, LIMIT 1), or make sure student_id in payments is unique.
• Don't forget, UPDATE ... FROM is a PostgreSQL-specific feature and doesn't work like this in some other DBMS (like MySQL).

Practical Examples

Example 1: Changing Student Status

Let's say students who finished the course get the status "graduate". We have a status column. To update the status for students with completed_course = true:

UPDATE students
SET status = 'graduate'
WHERE completed_course = true;

Example 2: Giving Employees a Raise

If you have an employees table and want to bump up the salary by 10% for everyone in the Sales department:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

Here we're using a math operation right in the SET section. Super handy for mass updates.

Example 3: Conditional Update

Sometimes you need to update data based on a condition. For example, if an employee's salary is less than 50,000, it should go up by 20%, otherwise by 10%:

UPDATE employees
SET salary = CASE
                WHEN salary < 50000 THEN salary * 1.20
                ELSE salary * 1.10
             END;

This command uses the CASE construct to set different update rules depending on the salary value.

Common Mistakes When Using UPDATE

You can probably guess the most popular mistake, right? Yep, it's forgetting the WHERE. Imagine this: you have a database with 10,000 employees, and you accidentally gave everyone a raise. Even if your coworkers love it, your boss probably won't.

Another common mistake is updating the wrong column. Always double-check what and where you're writing. A simple SELECT before UPDATE can save you a ton of time.