Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Операции по обновлению данных
Теперь, создав образец базы данных, мы покажем выполнение различных операций по обновлению данных с использованием оператора UPDATE и других команд в MySQL**.**
Если при использовании UPDATE вы не используете задающий условия параметр WHERE, то будут обновлены все строки в таблице. Предположим, в книжном магазине проходит акция «Всё по 500» — изменим цену всех книг на фиксированную 500 рублей:
UPDATE book
SET price=500;В результате выполнения запроса мы получим такую табличку:
Если мы попробуем присвоить значение, которое уже находится в столбце, то MySQL заметит это и не обновит его.
Если мы захотим присвоить значение NULL столбцу, при создании которого было указано NOT NULL, то запрос вернет ошибку:
Column 'name_column' cannot be nullВ то же время, если указать параметр IGNORE, то значение будет изменено на значение по умолчанию для конкретного типа: 0 для числовых, “” для символьных и «нулевое» для дат. Например, 0000 для типа данных YEAR или 0000-00-00 00:00:00 для типа DATETIME.
2. Обновление строк с условием
Изменение всех строк таблицы требуется довольно редко. Гораздо чаще нам необходимо обновить значения для какой-то конкретной записи или для нескольких. Допустим, мы хотим изменить строки по какому-то условию. Например, устроим распродажу книг, которых осталось в наличии меньше 5 штук. Для этого в MySQL с командой UPDATE используем оператор WHERE:
UPDATE book
SET price=300
WHERE amount < 5;Результат выполнения запроса:
Хорошо видно, что изменились только строки с книгами Dead Souls и Iliad, так как только они удовлетворяли прописанному в запросе условию.
3. Обновление значений с выражением
При обновлении мы можем задавать столбцу не только статичное значение, но и выражения. Предположим, в магазине проходит акция, и на книги русских писателей объявлена скидка в 15%:
UPDATE book
SET price=price * 0.85
WHERE author_id in (1,3);В таблице author имеется только два русских писателя – Leo Tolstoy и Nikolai Gogol с author_id 1 и 3 соответственно.
Результат выполнения запроса:
Обновление значений происходит в определенном порядке: слева направо. Например, следующий запрос сначала увеличит значение amount на 1, а потом удвоит его:
UPDATE book
SET amount = amount + 1, amount =amount*2;Результат выполнения запроса:
Также мы можем изменить значение строк на значения «по умолчанию» DEFAULT, которые задаются при создании или изменении таблицы. Для того чтобы узнать, какие значения в нашей таблице используются по умолчанию, выполним запрос:
В результате получим следующую структуру нашей таблицы в MySQL:
Заменим значения столбца amount на значение DEFAULT. Так как по умолчанию значение для amount было 0, мы должны получить все 0:
UPDATE book
SET amount=DEFAULT;Результат выполнения запроса соответствует ожиданиям:
5. Обновление нескольких столбцов
Используя один запрос, мы можем обновить сразу несколько столбцов. Например, изменим значения цены и количества у строк со значением book_id < 4:
UPDATE book
SET price=price*0.9,
amount = amount - 1
WHERE book_id < 4;Результат выполнения запроса:
Используя параметр LIMIT, мы можем ограничить количество записей, которые должны быть обновлены. Например, обновим только первую строку:
UPDATE book
SET price=100
WHERE genre_id = 4
LIMIT 1;В таблице имеется две строки с genre_id равным 4, но, так как мы указали LIMIT 1, обновится только одна:
Также следует отметить, что LIMIT N вовсе не означает, что мы обновим N строк. В результате запроса произойдёт обработка первых N строк, подходящих под условие WHERE, независимо от того, обновились эти строки в итоге или нет.
7. Обновление нескольких таблиц
В MySQL мы можем обновить сразу несколько таблиц:
UPDATE book, author
SET amount=amount + 3,
author.name_author = '-'
WHERE book.book_id = 4 and author.id = 4;Результат запроса в таблице book:
Результат запроса в таблице authors:
8. Обновление таблиц с объединением (INNER JOIN)
Во время обновления можно также объединять таблицы при помощи команды INNER JOIN.
UPDATE book b INNER JOIN author a
ON b.author_id = a.id
SET b.title = CONCAT(b.title, ' (', a.name_author,')');Указывать INNER необязательно, так как этот тип объединения используется по умолчанию. Запрос можно переписать следующим образом, и мы получим такой же результат:
UPDATE book, author a
SET b.title = CONCAT(b.title, ' (', a.name_author,')')
WHERE b.author_id = a.id;9. Обновление таблиц с объединением (LEFT JOIN)
Также мы можем использовать LEFT JOIN. В этом случае обязательно указывать, что мы используем именно левое объединение. Например, можно изменить на складе количество книг после их покупки. Добавим в таблицу sales две строки:
INSERT INTO sales (book_id, count, cost)
VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3),
(3, 1, (SELECT price FROM book WHERE book_id = 3)*1);Магазин продал 3 книги Anna Karenina и 1 книгу Dead Souls. Выполним запрос:
UPDATE book LEFT JOIN sales on book.book_id = sales.book_id
SET amount = amount - count
WHERE sales.book_id is not NULL;В итоге обновления видим, что количество книг на складе уменьшилось (для тех, которые мы продали):
Если мы попробуем не использовать LEFT JOIN, то получим ошибку «Out of range value for column 'amount' at row 3», т.к. amount не может быть отрицательным. Или, если добавить IGNORE, получим:
Как можно видеть в данном случае во всех строках количество уменьшилось на три книги, что нам не очень подходит.
10. Обновление с CASE, IF, IFNULL, COALESCE
При обновлении таблицы также возможно использовать условные операторы, такие как CASE, IF и т.д.
Функция CASE проверяет истинность набора условий и, в зависимости от результата, возвращает один из возможных результатов. Синтаксис при работе с UPDATE в MySQL для операторов CASE и WHEN будет следующий:
UPDATE book
SET price =
CASE genre_id
WHEN 1 THEN 100
WHEN 2 THEN 150
ELSE price
END;В данном случае, если книга имеет жанр 1 мы устанавливаем стоимость 100, если жанр 2 – стоимость 150.
Результат выполнения запроса:
Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Если книга имеет жанр 4, то мы уменьшаем ее стоимость на 200, иначе оставляем стоимость прежней:
UPDATE book
SET price = IF (genre_id = 4, price-200, price);Результат выполнения запроса:
Функция IFNULL проверяет значение выражения – если оно имеет значение NULL, то возвращается определенное значение, в противном случае возвращается само выражение. Пусть одно из значений amount оказалось NULL:
Проверим все значения в столбце amount, и если встретится NULL, заменим его на 0:
UPDATE book
SET amount = IFNULL(amount, 0);Результат выполнения запроса:
Функция COALESCE довольна похожа на IFNULL. Основная особенность заключается в том, что данная функция может принимать сразу несколько значений (два и более). Как и IFNULL, возвращает первое не равное NULL.
Для того чтобы посмотреть, как работает этот вариант, создадим таблицу следующего вида:
UPDATE test_table
SET col4 = COALESCE(col1, col2, col3, 'нет значения');Результат выполнения запроса:
11. Обновление с сортировкой
Сортировка может помочь при обновлении поля с уникальным ключом. Если мы захотим сдвинуть наши id на 1, то, изменив первую строку, мы получим две строки, у которых id = 2, и возникнет ошибка. Но если добавить ORDER BY и начать обновлять с конца, то запрос успешно выполнится:
UPDATE book
SET book_id=book_id+1Результат выполнения запроса:
12. Обновление на основе данных из других таблиц
Также в MySQL при работе с UPDATE в условии WHERE возможно использовать вложенные команды SELECT и FROM. В рассматриваемом примере мы сначала получаем идентификатор жанра 'Epic poetry', а потом на основе полученного значения отбираем строки для обновления таблицы.
UPDATE book
SET amount = 0
WHERE genre_id = (
SELECT id
FROM genre
Where name_genre = 'Epic poetry'
);Как вариант, мы можем выбрать значения, которые нужно изменить, используя запрос:
UPDATE book
SET price = (
SELECT MIN (cost)
FROM sales)
WHERE amount < 5;Мы изменяем значения price всех книг, у которых количество на складе меньше 5, на минимальную сумму продажи.
Минимальная сумма продаж у нас 480:
В этой ситуации невозможно обновить таблицу, выбрав значения из той же таблицы в подзапросе. Но есть вариант использовать небольшую хитрость – мы можем объединить таблицу с собой:
UPDATE book AS book_1
INNER JOIN(
SELECT genre_id, MIN(amount) AS min_amount
FROM book
GROUP BY genre_id
) AS book_2 ON book_1.genre_id = book_2.genre_id
SET book_1.amount = book_2.min_amount;В данном случае подзапрос создает временную таблицу для присоединения и закрывает ее до того, как начнется выполнение UPDATE.
Подзапрос находит минимальное количество книг для каждого жанра, после чего используется для обновления столбца amount. В нашей таблице только у жанра 4 имеется больше одной строки. Значения в обеих строках должно замениться на минимальное для этого жанра – 4.
Результат выполнения запроса:
Есть еще один вариант обновления – использование SELECT FROM SELECT :
UPDATE book AS book_1
SET book_1.price = (SELECT MIN(price) AS min_price FROM (
SELECT price
FROM book) as book_2);В данном случае также создается временная таблица. Однако в этом случае присваивается только одно значение всем строкам.
Разверните базу данных в облаке в один клик
Мы постарались максимально подробно раскрыть особенности применения оператора UPDATE в MySQL. Но, конечно, практическое применение может продемонстрировать немало других интересных вариантов.
Нужен сервер для практики? Закажите VPS на Simple-Server — root-доступ, NVMe, DDoS-защита и поддержка 24/7.