Обновление данных, оператор UPDATE

Добавленные данные не всегда остаются верными: люди меняют фамилии, в платежах находят опечатки, цены приходится пересчитывать. Чтобы исправить строку, не удаляя её, используют оператор UPDATE.

Общая структура запроса с оператором UPDATE

UPDATE имя_таблицы
SET поле_таблицы = значение [, ...]
[WHERE условие_выборки];

Например, Энди (Andie Quincey) сменил фамилию: теперь он Andie Anthony.

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

Изменение нескольких столбцов

Чтобы изменить сразу несколько столбцов, перечислите пары поле_таблицы = значение после SET через запятую. Например, Энди купил 4 упаковки молока по 25, а в платёж с идентификатором 3 по ошибке записали 5 по 20. Исправим оба столбца одним запросом:

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

Все перечисленные столбцы изменятся в одних и тех же строках — тех, что подходят под условие WHERE.

Вычисляемые значения

Новое значение можно вычислить из старого — например, удвоить все цены:

MySQL 8.1
UPDATE Payments
SET unit_price = unit_price * 2;

В выражении можно использовать и другие столбцы той же строки, если их типы совместимы. Например, запишем платёж с идентификатором 3 одной позицией: в unit_price окажется стоимость всей покупки, а в amount — единица.

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;

Обновление по данным другой таблицы

Иногда строки для обновления нужно выбрать по данным из другой таблицы. Например, выяснилось, что во всех платежах за икру цена записана вдвое меньше настоящей. Самый простой способ — подзапрос в 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 имя_таблицы_1
JOIN имя_таблицы_2 ON условие_соединения
SET имя_таблицы_1.поле_таблицы = значение [, ...]
[WHERE условие_выборки];

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 имя_таблицы_1
SET поле_таблицы = значение [, ...]
FROM имя_таблицы_2
WHERE условие_соединения
[AND условие_выборки];

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
);

Типичные ошибки

Забытый WHERE

Запрос без условия изменит все строки таблицы. Сначала проверьте условие запросом SELECT, а потом подставьте его в UPDATE.

AND вместо запятой в SET

Запись ниже не меняет два столбца: всё, что стоит справа от первого =, считается одним логическим выражением.

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

Чтобы изменить оба столбца, присваивания разделяют запятой. По ней SET понимает, где заканчивается одно и начинается следующее:

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

Сравнение с NULL через =

Условие WHERE поле = NULL не подходит ни одной строке, поэтому запрос ничего не изменит. Для проверки на пустое значение используйте IS NULL.

Проверьте себя

У некоторых пользователей в таблице Users не указана дата подтверждения почты: поле email_verified_at равно NULL. Сколько строк изменит этот запрос?

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