Работа с командой UPDATE — как обновить данные в таблице MySQL
9 подробных примеров о том, как обновить данные в MySQL с помощью 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 >
UPDATE products SET price = 50500 WHERE >Результат:
+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 50500 | 1 | +----+------------------+-------------+----------+-------+----------+ Теперь покажем, как обновить сразу несколько полей в одной строке. Например, у товара одновременно изменилась стоимость и количество. Вместо того, чтобы писать два отдельных запроса, мы укажем сразу оба изменяемых поля и их значения:
UPDATE products SET price = 78500, quantity = 3 WHERE >Результат:
+----+------------------+-------------+----------+-------+----------+ | 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 >Но СУБД вернет ошибку: Duplicate entry ’2′ for key ’products.PRIMARY’. Так происходит потому, что операция UPDATE начинает обрабатывать строки последовательно, начиная с первой. И получается, что она пытается обновить идентификатор в первой строке с 1 на 2. Но сейчас в БД уже есть запись с ключом = 2, поэтому появляется ошибка. Чтобы этого избежать, мы можем использовать ORDER BY и начать обновлять таблицу с конца:
UPDATE products SET 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 запроса обновить данные в таблице. Теперь вы сможете на основе этих примеров писать команды для своей структуры таблиц.
Insert в MySQL — добавление данных в таблицу
Как можно изменить данные в SQL?
Описание БД В базе данных должны записываться даты начала и завершения каждого восхождения, имена и адреса участвовавших в нем альпинистов, название и высота горы, страна и район, где эта гора расположена. Как можно предоставить возможность изменить данные о вершине, если на нее не было восхождения?
/* Создание таблицы участников*/ CREATE TABLE ALPINIST ( KOD_UCHASTNIKA SERIAL PRIMARY KEY, NAME_F character(20), NAME_E character(20), ADRES text ); /* Создание таблицы восхождений*/ CREATE TABLE VOSHOD ( KOD_VOSHOD SERIAL PRIMARY KEY, NAZVANIE_GRUPPU text, START_V date, END_V date, ID_GORU SERIAL ); /* Создание таблицы ГОР*/ CREATE TABLE GOR ( KOD_GORU SERIAL PRIMARY KEY, NAZVANIE_GRUPPU text, H SERIAL, RASPOLOZENIE text ); /* Создание таблицы УЧАСТНИКИ ВОСХОЖДЕНИЙ*/ CREATE TABLE UCH_VOSHOD ( KOD_UCH SERIAL PRIMARY KEY, VOSHOD SERIAL, ALPINIST SERIAL ); Отслеживать
задан 29 апр 2021 в 7:05
75 8 8 бронзовых знаков
Правильно добавить в структуру внешние ключи с соответствующим ON DELETE CASCADE, после чего просто удалить все записи в таблице восхождений, где восхождения выполняются на нужную гору.
29 апр 2021 в 7:21
Структура таблиц — ошибочная. Только поля, объявленные как PRIMARY KEY, должны быть SERIAL. Остальные — INTEGER (возможно, NOT NULL). А то в схеме создаётся хренова гора ненужных генераторов — слишком несуразная цена для создание UNSIGNED поля, проще обойтись CHECK constraint (впрочем, внешние ключи делают такое ограничение ненужным).
Как изменить данные в таблице на основании данных другой таблицы sql?
есть таблица table1, в ней есть столбец total
есть таблица table2, в ней также есть столбец total2
Задача состоит в том что бы из столбца total отнять значение столбца total2
Пытаюсь сделать так:
mysqli_query($connect, "UPDATE `table1` SET `total` = ('total' - (SELECT total2 FROM table2 where `table.name` = `table2.name`) ) ");Однако данный код не работает. Пытался просто обновить значения на основании данных другой таблицы :
mysqli_query($connect, "UPDATE `table1` SET `total` = (SELECT total2 FROM table2 where `table.name` = `table2.name`) ");Но так же безрезультатно. Подскажите пожалуйста в чем может быть проблема. Может ошибка в синтаксисе или вообще не правильно делаю.
- Вопрос задан более двух лет назад
- 991 просмотр
Как поменять данные в таблице sql
(SELECT task.employee_id FROM task WHERE task.exp_date > ‘2022-10-01’); это ни разу не выдаст список не просроченных задач. это выдаст список задач, у которых дедлайн истекает после этой даты ��
Надежда Уровень 94 Expert
30 августа 2023
Непонятно, почему автор пишет, что такой подход работать не будет. UPDATE user SET level = 80 WHERE user.name IN (SELECT employee.name FROM employee WHERE salary >= 50000); Все отлично работает. Только запятую после 80 не надо ставить.
Надежда Уровень 94 Expert
30 августа 2023
Мда. живи теперь с этим. Не проще ли было написать запрос, чтобы он укладывался в рамки ранее изученного? Например так: UPDATE user JOIN (SELECT name FROM employee WHERE salary >= 50000) AS good_emps ON user.name = good_emps.name SET user.level = 80;
Павел К Уровень 34
18 апреля 2023
(SELECT * FROM employee WHERE salary >= 50000) good_emps
или должно быть
(SELECT * FROM employee WHERE salary >= 50000) AS good_emps
Сергей Уровень 111 Expert
15 декабря 2022
SET qwerty = 111 — нужны пробелы, иначе задачи не примет валидатор. SET qwerty=111 — так не примет, 6 попыток на первой задаче 😀
Эдуард Уровень 108 Expert
26 октября 2022
Задача 0614. Сначала пишут что такой подход работать не будет, и тут же дают задачу с ним.
Сообщество
JavaRush — это интерактивный онлайн-курс по изучению Java-программирования c нуля. Он содержит 1200 практических задач с проверкой решения в один клик, необходимый минимум теории по основам Java и мотивирующие фишки, которые помогут пройти курс до конца: игры, опросы, интересные проекты и статьи об эффективном обучении и карьере Java‑девелопера.
Подписывайтесь
Язык интерфейса
«Программистами не рождаются» © 2023 JavaRush
Скачивайте наши приложения
«Программистами не рождаются» © 2023 JavaRush
Этот веб-сайт использует данные cookie, чтобы настроить персонально под вас работу сервиса. Используя веб-сайт, вы даете согласие на применение данных cookie. Больше подробностей — в нашем Пользовательском соглашении.