Updating Data

Once data is inserted into a database, it rarely stays static. Users change their email addresses, products change their prices, and orders change their status from “Pending” to “Shipped”.

To modify existing data, we use the UPDATE statement.

The Syntax

An UPDATE statement tells the database which table to modify, what the new values should be, and crucially, which specific rows should be changed.

UPDATE users 
SET email = 'new.email@example.com' 
WHERE id = 101;

Let’s break this down:

UPDATE users: Specifies the target table.
SET email = ...: Tells the database which column to change, and the new value to assign to it.
WHERE id = 101: The most important part. This tells the database exactly which row (or rows) to modify.

Updating Multiple Columns

You can update more than one column at the same time by separating the assignments with a comma.

UPDATE users 
SET email = 'new@example.com', country = 'Canada' 
WHERE id = 101;

The Danger of UPDATE without WHERE

Take a very close look at the following query:

UPDATE products 
SET price = 0;

What will this do? Because there is no WHERE clause to filter the target rows, this will set the price of EVERY SINGLE PRODUCT IN the DATABASE to 0!

This is a catastrophic mistake that has ruined many junior developers’ weekends.

[!CAUTION] Unless you explicitly intend to modify every single row in a table, NEVER execute an UPDATE statement without a WHERE clause. A good habit is to write the WHERE clause first before filling out the SET clause!

You can also use operators in your SET clause. For example, to give a 10% raise to everyone in the Engineering department: UPDATE employees SET salary = salary * 1.10 WHERE department = 'Engineering';

Try safely updating the price of a product in the interactive lab!