Updating data, UPDATE operator

Data that has been added does not always stay correct: people change their surnames, typos turn up in payments, prices need to be recalculated. To fix a row without deleting it, use the UPDATE operator.

General query structure with the UPDATE operator

UPDATE table_name
SET table_field = value [, ...]
[WHERE condition];

For example, Andie (Andie Quincey) has changed his surname and is now Andie Anthony.

MySQL 8.1
UPDATE FamilyMembers
SET member_name = 'Andie Anthony'
WHERE member_name = 'Andie Quincey';

Changing several columns

To change several columns at once, list the table_field = value pairs after SET, separated by commas. For example, Andie bought 4 cartons of milk at 25 each, but the payment with ID 3 was recorded by mistake as 5 at 20. Let's fix both columns in one query:

MySQL 8.1
UPDATE Payments
SET amount = 4, unit_price = 25
WHERE payment_id = 3;

All the listed columns change in the same rows — the ones that match the WHERE condition.

Calculated values

A new value can be calculated from the old one — for example, to double all prices:

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2;

The expression can also use other columns of the same row, as long as their types are compatible. For example, let's record the payment with ID 3 as a single item: unit_price will hold the cost of the whole purchase, and amount will be one.

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * amount,
    amount = 1
WHERE payment_id = 3;

MySQL 8.1
UPDATE Payments
SET amount = 1,
    unit_price = unit_price * amount
WHERE payment_id = 3;

MySQL 8.1
UPDATE Payments
SET amount = 1,
    unit_price = unit_price * amount
WHERE payment_id = 3;

Updating based on data from another table

Sometimes the rows to update have to be selected using data from another table. For example, it turned out that every caviar payment was recorded at half the real price. The simplest way is a subquery in WHERE:

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2
WHERE good IN (
    SELECT good_id
    FROM Goods
    WHERE good_name LIKE '%caviar%'
);

UPDATE table_name_1
JOIN table_name_2 ON join_condition
SET table_name_1.table_field = value [, ...]
[WHERE condition];

MySQL 8.1
UPDATE Payments
JOIN Goods ON Payments.good = Goods.good_id
SET Payments.unit_price = Payments.unit_price * 2
WHERE Goods.good_name LIKE '%caviar%';

UPDATE table_name_1
SET table_field = value [, ...]
FROM table_name_2
WHERE join_condition
[AND condition];

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2
FROM Goods
WHERE Payments.good = Goods.good_id
    AND Goods.good_name LIKE '%caviar%';

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2
WHERE unit_price < (SELECT AVG(unit_price) FROM Payments);

MySQL 8.1
ERROR 1093 (HY000): You can't specify target table 'Payments' for update in FROM clause

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2
WHERE unit_price < (
    SELECT avg_price
    FROM (SELECT AVG(unit_price) AS avg_price FROM Payments) AS stats
);

Common mistakes

Forgotten WHERE

A query without a condition changes every row of the table. Check the condition with a SELECT query first, and then put it into UPDATE.

AND instead of a comma in SET

The query below does not change two columns: everything to the right of the first = is treated as a single logical expression.

MySQL 8.1
UPDATE Payments
SET amount = 4 AND unit_price = 25
WHERE payment_id = 3;

MySQL 8.1
ERROR: argument of AND must be type boolean, not type integer

To change both columns, separate the assignments with a comma. That is how SET knows where one ends and the next one begins:

MySQL 8.1
UPDATE Payments
SET amount = 4, unit_price = 25
WHERE payment_id = 3;

Comparing with NULL using =

The condition WHERE field = NULL matches no rows, so the query changes nothing. To check for an empty value, use IS NULL.

Test yourself

Some users in the Users table have no email confirmation date: their email_verified_at field is NULL. How many rows will this query change?

MySQL 8.1
UPDATE Users
SET email_verified_at = '2024-01-01'
WHERE email_verified_at = NULL;