Работа с командой UPDATE — как обновить данные в таблице MySQL

Введение

В этой статье мы расскажем, для чего нужна команда UPDATE в MySQL и покажем как ей пользоваться на нескольких примерах.

UPDATE: что это за команда и зачем она нужна

UPDATE — это команда, которая обновляет данные в таблице. Ее общий синтаксис такой: 

    UPDATE [table] table_name
SET column1 = value1, column2 = value2, ... 
[WHERE condition]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

Сначала мы указываем обязательные параметры: название таблицы, названия колонок и нужные значения для обновления. Обратите внимание, что в MySQL можно использовать ключевое слово table (update table), а можно его опустить и сразу указать название таблицы.

Затем идут необязательные блоки WHERE (условие обновления), ORDER BY (сортировка) и LIMIT (ограничение количества обновляемых записей).

Создание облачной базы данных MySQL

Для начала нам понадобится сервер MySQL. На примере платформы Selectel мы покажем, как создать базу данных и подключиться к ней. Мы создадим управляемую БД — это позволит быстро создать сервер, и не заниматься его настройкой.

Управляемая база данных в облаке

Если у вас уже есть развернутый сервер MySQL, можете сразу переходить к следующему разделу.

Итак, в панели управления заходим в раздел «Облачная платформа» — «Базы данных», нажимаем кнопку «Создать кластер».

На следующем экране выбираем параметры создаваемой БД. Выбираем «СУБД» — MySQL. Далее выбираем конфигурацию сервера, нам будет достаточно 2 vCPU, 4 ГБ оперативной памяти и 32 ГБ диска. Важно выбрать публичную сеть, чтобы к серверу можно было подключаться из интернета.

Ждем несколько минут, пока сервер создается. После того, как он перейдет в статус ACTIVE, можно продолжать настройку.

Нажимаем на имя кластера и переходим на вкладку «Пользователи». Тут нужно создать пользователя для доступа к БД. Запишите его имя и пароль, они понадобятся для подключения.

Затем переходим на вкладку «Базы данных», тут нужно создать БД (схему), в которой мы будем работать.

Когда БД создастся, нужно дать к ней доступ пользователю, которого мы создали на предыдущем шаге. Для этого нажмите на название БД и выберите пользователя.

Затем перейдите на вкладку «Настройки» и запишите DNS- или IP-адрес сервера, он нужен для подключения.

Все, сервер MySQL готов к работе, подключитесь к нему. В рамках этой статьи мы ограничимся обычным консольным клиентом mysql. Но вы можете использовать любые другие способы, которые позволяют писать SQL-команды. Например, в нашем блоге есть статья про инструмент MySQL Workbench.

Структура таблиц

Мы будем рассматривать работу с командой UPDATE на примере очень простой схемы БД. Представим, что мы — сеть магазинов бытовой техники. У нас есть 3 таблицы:

• categories: таблица категорий товаров. В ней хранятся только идентификаторы и названия категорий.

    +----+------------------+
| id | name             |
+----+------------------+
|  1 | Ноутбуки         |
|  2 | Планшеты         |
|  3 | Телефоны         |
+----+------------------+

• stores: таблица магазинов. В ней хранятся идентификаторы, названия магазинов, город и адрес.

    +----+------------------+---------------------+----------------------+
| id | name             | city                | address              |
+----+------------------+---------------------+----------------------+
|  1 | Магазин 1        | Санкт-Петербург     | Цветочная 63         |
|  2 | Магазин 2        | Санкт-Петербург     | Советская 20         |
|  3 | Магазин 3        | Москва              | Берзарина 42         |
|  4 | Магазин 4        | Москва              | Авиамоторная 96      |
+----+------------------+---------------------+----------------------+

• products: таблица товаров. В ней хранятся идентификаторы, названия товаров, ссылки на категорию товара и магазин, цена товара и его количество.

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 50000 |        1 |
|  2 | Ноутбук 2        |           1 |        3 | 79000 |        2 |
|  3 | Планшет 1        |           2 |        2 |  8000 |        5 |
|  4 | Планшет 2        |           2 |        4 | 12000 |        3 |
|  5 | Телефон 1        |           3 |        1 | 18000 |        5 |
|  6 | Телефон 2        |           3 |        2 | 25000 |        1 |
|  7 | Телефон 3        |           3 |        3 | 78000 |        1 |
+----+------------------+-------------+----------+-------+----------+

Мы не будем показывать, как создать таблицы, это выходит за рамки статьи. Мы писали отдельную статью про работу с таблицами — почитайте, если вы еще не умеете создавать их.

Пример 1: обновление одной строки

Допустим, мы хотим изменить стоимость одного конкретного товара. Для этого указываем имя MySQL таблицы (update products), название обновляемого поля и значение (set price = 50500), а также условие, какую именно стоку нужно обновить (where id = 1):

    UPDATE products 
SET price = 50500 
WHERE id = 1;

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 50500 |        1 |
+----+------------------+-------------+----------+-------+----------+

Теперь покажем, как обновить сразу несколько полей в одной строке. Например, у товара одновременно изменилась стоимость и количество. Вместо того, чтобы писать два отдельных запроса, мы укажем сразу оба изменяемых поля и их значения:

    UPDATE products 
SET price = 78500, quantity = 3 
WHERE id = 2;

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  2 | Ноутбук 2        |           1 |        3 | 78500 |        3 |
+----+------------------+-------------+----------+-------+----------+

Пример 2: обновление всех строк в таблице

Также можно обновить сразу несколько строк в одной таблице, или даже все. Например, мы хотим выровнять количество всех товаров во всех магазинах, и установить его равным трем. Для этого нам нужно просто опустить блок WHERE, и тогда оператор UPDATE применит все, что указано в блоке SET сразу ко всем строкам в таблице:

    UPDATE products 
SET quantity = 3;

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 50500 |        3 |
|  2 | Ноутбук 2        |           1 |        3 | 78500 |        3 |
|  3 | Планшет 1        |           2 |        2 |  8000 |        3 |
|  4 | Планшет 2        |           2 |        4 | 12000 |        3 |
|  5 | Телефон 1        |           3 |        1 | 18000 |        3 |
|  6 | Телефон 2        |           3 |        2 | 25000 |        3 |
|  7 | Телефон 3        |           3 |        3 | 78000 |        3 |
+----+------------------+-------------+----------+-------+----------+

Пример 3: обновление с выражением

Вместо конкретного значения можно использовать выражение, на основании которого будет вычисляться значение. Например, в магазинах проходит акция, и нужно снизить цены всех ноутбуков на 10%. Рассчитывать значение вручную для каждого товара неудобно, поэтому мы используем выражение. Укажем, что цену нужно умножить на 0.9, то есть сделать ее равной 90% от начальной:

    UPDATE products 
SET price = (price * 0.9);

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 45450 |        3 |
|  2 | Ноутбук 2        |           1 |        3 | 70650 |        3 |
|  3 | Планшет 1        |           2 |        2 |  7200 |        3 |
|  4 | Планшет 2        |           2 |        4 | 10800 |        3 |
|  5 | Телефон 1        |           3 |        1 | 16200 |        3 |
|  6 | Телефон 2        |           3 |        2 | 22500 |        3 |
|  7 | Телефон 3        |           3 |        3 | 70200 |        3 |
+----+------------------+-------------+----------+-------+----------+

Пример 4: обновление на основе данных из других таблиц

В условии WHERE можно использовать данные, полученные из других таблиц. Расширим предыдущий пример с выражением. В этот раз акция проходит только в магазине с названием «Магазин 2». Для этого нужно в условии WHERE указать идентификатор магазина, для которого нужно обновить цены. Но вместо того, чтобы искать в таблице идентификатор и потом подставлять его в SQL-запрос, мы можем указать название, а идентификатор подставится сам.

Мы используем вложенный оператор SELECT, который сначала вернет идентификатор нужного нам магазина, а затем подставит его в блок WHERE:

    UPDATE products
SET price = (price * 0.9)
WHERE store_id = (
   SELECT id
   FROM stores
   WHERE name = 'Магазин 2'
);

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 45450 |        3 |
|  2 | Ноутбук 2        |           1 |        3 | 70650 |        3 |
|  3 | Планшет 1        |           2 |        2 |  6480 |        3 |
|  4 | Планшет 2        |           2 |        4 | 10800 |        3 |
|  5 | Телефон 1        |           3 |        1 | 16200 |        3 |
|  6 | Телефон 2        |           3 |        2 | 20250 |        3 |
|  7 | Телефон 3        |           3 |        3 | 70200 |        3 |
+----+------------------+-------------+----------+-------+----------+

То же самое можно сделать с помощью оператора JOIN:

    UPDATE products
JOIN stores ON stores.id = store_id
SET price = (price * 0.9)
where stores.name = 'Магазин 2';

Пример 5: ключевое слово DEFAULT

В блоке SET вместо указания конкретного значения можно использовать ключевое слово DEFAULT, которое установит для этого столбца значение по умолчанию. Если для столбца не указано значение по умолчанию, тогда значение будет равно NULL.

В нашей таблице products у поля quantity есть значение по умолчанию, оно равно 1. Поэтому когда мы выполним следующую команду, количество всех товаров станет равным единице:

    UPDATE products
SET quantity = DEFAULT;

Результат:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 45450 |        1 |
|  2 | Ноутбук 2        |           1 |        3 | 70650 |        1 |
|  3 | Планшет 1        |           2 |        2 |  6480 |        1 |
|  4 | Планшет 2        |           2 |        4 | 10800 |        1 |
|  5 | Телефон 1        |           3 |        1 | 16200 |        1 |
|  6 | Телефон 2        |           3 |        2 | 20250 |        1 |
|  7 | Телефон 3        |           3 |        3 | 70200 |        1 |
+----+------------------+-------------+----------+-------+----------+

Пример 6: обновление значений в нескольких таблицах

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

    UPDATE stores, products
SET stores.address = 'Пятницкая 23', products.quantity = 3
WHERE stores.id = 4
and products.store_id = 4;

Результат, таблица stores:

    +----+------------------+---------------------+----------------------+
| id | name             | city                | address              |
+----+------------------+---------------------+----------------------+
|  1 | Магазин 1        | Санкт-Петербург     | Цветочная 63         |
|  2 | Магазин 2        | Санкт-Петербург     | Советская 20         |
|  3 | Магазин 3        | Москва              | Берзарина 42         |
|  4 | Магазин 4        | Москва              | Пятницкая 23         |
+----+------------------+---------------------+----------------------+

Результат, таблица products:

    +----+------------------+-------------+----------+-------+----------+
| id | name             | category_id | store_id | price | quantity |
+----+------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1        |           1 |        1 | 45450 |        1 |
|  2 | Ноутбук 2        |           1 |        3 | 70650 |        1 |
|  3 | Планшет 1        |           2 |        2 |  6480 |        1 |
|  4 | Планшет 2        |           2 |        4 | 10800 |        3 |
|  5 | Телефон 1        |           3 |        1 | 16200 |        1 |
|  6 | Телефон 2        |           3 |        2 | 20250 |        1 |
|  7 | Телефон 3        |           3 |        3 | 70200 |        1 |
+----+------------------+-------------+----------+-------+----------+

Пример 7: ограничение количества обновляемых строк

Ключевое слово LIMIT позволяет ограничить количество обновляемых строк, не смотря на то, сколько на самом деле строк подошло под условие WHERE. Например, этот запрос дописать к названиям всех товаров примечание «(2020 год)». Но так как мы указали ограничение LIMIT 1, то обновится только одна строка:

    UPDATE products
SET name = CONCAT(name, ' (2020 год)')
LIMIT 1;

Результат:

    +----+-----------------------+-------------+----------+-------+----------+
| id | name                  | category_id | store_id | price | quantity |
+----+-----------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1 (2020 год)  |           1 |        1 | 45450 |        1 |
|  2 | Ноутбук 2             |           1 |        3 | 70650 |        1 |
|  3 | Планшет 1             |           2 |        2 |  6480 |        1 |
|  4 | Планшет 2             |           2 |        4 | 10800 |        3 |
|  5 | Телефон 1             |           3 |        1 | 16200 |        1 |
|  6 | Телефон 2             |           3 |        2 | 20250 |        1 |
|  7 | Телефон 3             |           3 |        3 | 70200 |        1 |
+----+-----------------------+-------------+----------+-------+----------+

Пример 8: условные операторы IF и CASE

Иногда значение в блоке SET может быть не явным, а зависеть от какого-либо условия. Например, мы хотим уменьшить цены на все ноутбуки на 100р. Мы уже знаем, как это можно сделать с помощью условия WHERE. А теперь покажем, как то же самое можно сделать с помощью условного оператора IF:

    UPDATE products
SET price = IF(category_id=1, price-100, price);

Результат:

    +----+-----------------------+-------------+----------+-------+----------+
| id | name                  | category_id | store_id | price | quantity |
+----+-----------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1 (2020 год)  |           1 |        1 | 45350 |        1 |
|  2 | Ноутбук 2             |           1 |        3 | 70550 |        1 |
|  3 | Планшет 1             |           2 |        2 |  6480 |        1 |
|  4 | Планшет 2             |           2 |        4 | 10800 |        3 |
|  5 | Телефон 1             |           3 |        1 | 16200 |        1 |
|  6 | Телефон 2             |           3 |        2 | 20250 |        1 |
|  7 | Телефон 3             |           3 |        3 | 70200 |        1 |
+----+-----------------------+-------------+----------+-------+----------+

Усложним пример. Теперь нам нужно уменьшить цены на ноутбуки на 100р, на планшеты поднять на 100р, а телефоны — уменьшить на 5%. Для этого лучше подойдет другой условный оператор — CASE. В нем мы можем перечислить сразу несколько условий:

    UPDATE products SET price = CASE
   WHEN category_id = 1 THEN price-100
   WHEN category_id = 2 THEN price+100
   WHEN category_id = 3 THEN price*0.95
   END;

Результат:

    +----+-----------------------+-------------+----------+-------+----------+
| id | name                  | category_id | store_id | price | quantity |
+----+-----------------------+-------------+----------+-------+----------+
|  1 | Ноутбук 1 (2020 год)  |           1 |        1 | 45250 |        1 |
|  2 | Ноутбук 2             |           1 |        3 | 70450 |        1 |
|  3 | Планшет 1             |           2 |        2 |  6580 |        1 |
|  4 | Планшет 2             |           2 |        4 | 10900 |        3 |
|  5 | Телефон 1             |           3 |        1 | 15390 |        1 |
|  6 | Телефон 2             |           3 |        2 | 19238 |        1 |
|  7 | Телефон 3             |           3 |        3 | 66690 |        1 |
+----+-----------------------+-------------+----------+-------+----------+

Пример 9: сортировка ORDER BY

В MySQL сортировка ORDER BY обычно используется в операторе SELECT, но также ее можно использовать и в UPDATE. В этом случае записи будут обновляться в указанном порядке. Вот одна из ситуаций, когда это может пригодиться.

Допустим, мы хотим обновить идентификаторы всех товаров — увеличить на единицу. Для этого мы напишем такую команду:

    UPDATE products
SET id = (id+1);

Но СУБД вернет ошибку: Duplicate entry ’2′ for key ’products.PRIMARY’. Так происходит потому, что операция UPDATE начинает обрабатывать строки последовательно, начиная с первой. И получается, что она пытается обновить идентификатор в первой строке с 1 на 2. Но сейчас в БД уже есть запись с ключом = 2, поэтому появляется ошибка. Чтобы этого избежать, мы можем использовать ORDER BY и начать обновлять таблицу с конца:

    UPDATE products
SET id = (id+1)
ORDER BY id DESC;

Результат:

    
+----+-----------------------+-------------+----------+-------+----------+
| id | name                  | category_id | store_id | price | quantity |
+----+-----------------------+-------------+----------+-------+----------+
|  2 | Ноутбук 1 (2020 год)  |           1 |        1 | 45250 |        1 |
|  3 | Ноутбук 2             |           1 |        3 | 70450 |        1 |
|  4 | Планшет 1             |           2 |        2 |  6580 |        1 |
|  5 | Планшет 2             |           2 |        4 | 10900 |        3 |
|  6 | Телефон 1             |           3 |        1 | 15390 |        1 |
|  7 | Телефон 2             |           3 |        2 | 19238 |        1 |
|  8 | Телефон 3             |           3 |        3 | 66690 |        1 |
+----+-----------------------+-------------+----------+-------+----------+

Заключение

Мы познакомились с командой UPDATE, рассмотрели различные варианты ее использования и подкрепили знания на примерах. Вы узнали, как с помощью SQL запроса обновить данные в таблице. Теперь вы сможете на основе этих примеров писать команды для своей структуры таблиц.