SQL, how to update data

The data stored in a table can be updated using the UPDATE command. You tell the database which table to touch, which columns to change, and which rows to change them in:

UPDATE people SET age = 2 WHERE name = 'Roger'

This finds the row where name is 'Roger' and sets its age column to 2. Every other row stays untouched.

The database tells you how many rows it changed. If you see UPDATE 1, one row matched and was updated. If you see UPDATE 0, the WHERE clause matched nothing, and nothing happened. No error is raised, so check that count.

How to update more than one column

You can set several columns in a single statement, separating them with commas:

UPDATE people SET age = 3, name = 'Roger Jr.' WHERE name = 'Roger'

Using the current value

The new value doesn’t have to be a fixed one. You can compute it from the current value of the column. This adds one year to everyone named Roger:

UPDATE people SET age = age + 1 WHERE name = 'Roger'

The same works for strings, numbers, dates, anything the column holds.

What happens without WHERE?

Here is the classic mistake. It’s important to add the WHERE clause, otherwise this instruction:

UPDATE people SET age = 2

would update all rows in the table. Every single person now has age set to 2, and there is no undo.

This is not a syntax error. The database does exactly what you asked. It’s one of the most common ways to destroy data, and it takes one missing line.

How to protect yourself

My advice: before running an UPDATE, run a SELECT with the same WHERE clause:

SELECT * FROM people WHERE name = 'Roger'

The rows you get back are the rows the update will touch. If you see more rows than expected, you just saved yourself.

On an important database, wrap the update in a transaction. You can look at the result and roll back if it’s wrong:

BEGIN;
UPDATE people SET age = 2 WHERE name = 'Roger';
SELECT * FROM people;
ROLLBACK;

Replace ROLLBACK with COMMIT when the result looks right.