Changing Rows with UPDATE

Data doesn't only grow, it also changes: a customer moves, a game gets a new price. UPDATE changes rows that are already in a table. Oscar King has moved from Bristol to London:

UPDATE customers
SET city = 'London'
WHERE id = 10;

After UPDATE comes the table. After SET comes the column and, after the equals sign, its new value. And WHERE says which rows to change, exactly as in a query: the statement changes every row that the condition finds, and no other. Results shows Rows changed: 1, and a query shows the change:

SELECT name, city
FROM customers
WHERE city = 'London';

To change several columns at once, separate them with commas: SET city = 'London', email = 'oscar@king.dev'. In SET, the equals sign means “store this value”, while in WHERE, it compares. That's also how you clear a value: SET city = NULL (but to find empty values, you write WHERE city IS NULL, as you know).

To change one particular row, find it by its primary key, as above with WHERE id = 10. Names can repeat (two customers can both be called Oscar King) and text must match exactly, but an ID always means one row.

In the WHERE of an UPDATE, you can use everything you know: AND, OR, IN, LIKE, BETWEEN, functions and even subqueries. An UPDATE can change one row, many rows or none: when the condition finds nothing, that's not an error, the statement just changes nothing (Rows changed: 0).

Typical mistake: a WHERE that finds nothing, for example because of a typo: WHERE name = 'Oscar Kng'. You get no error, and the result of your query looks as if nothing had happened. When the change isn't there, look at Rows changed: if it says 0, the WHERE didn't find any row.