Для синтаксиса с одной таблицей, оператор UPDATE
обновляет столбцы существующих строк новыми значениями. Предложение SET
указывает, какие столбцы следует изменить, и значения, которые им следует присвоить. Каждое значение может быть задано в виде выражения или ключевого слова DEFAULT
. Оператор WHERE
, если он задан, указывает условия, определяющие, какие строки следует обновить.
Для синтаксиса с несколькими таблицами UPDATE
обновляет строки в каждой таблице, удовлетворяющей условиям объединения. Каждая совпадающая строка обновляется один раз, даже если она соответствует условиям несколько раз. Для синтаксиса с несколькими таблицами нельзя использовать ORDER BY
и LIMIT
.
UPDATE
;WHERE
при обновление записей таблицы;expr
;DEFAULT
;LIMIT
при обновлении данных;ORDER BY
при обновлении данных;IF
и CASE
при обновлении данных;UPDATE
.-- обновление данных одной таблицы UPDATE [IGNORE] tbl_name SET col1 = val, col2 = [expr|DEFAULT] , ... WHERE condition ORDER BY expr [ ASC | DESC ] LIMIT count; -- обновление данных нескольких таблиц UPDATE [IGNORE] tbl1, tbl2, ... SET tbl1.col1 = [val|expr|DEFAULT], tbl2.col1 = [val|expr|DEFAULT] , ... WHERE tbl1.col = tbl2.col
UPDATE
:Условия в необязательном операторе WHERE определяют, какие строки следует обновить. ВНИМАНИЕ! Если при использовании инструкции UPDATE
не использовать задающий условия оператор WHERE condition
, то будут обновлены все строки в таблице tbl_name
.
Если присвоить значение NULL
столбцу, при создании которого было указано NOT NULL
, то запрос вернет ошибку. Если при этом указать параметр IGNORE
, то значение будет изменено на значение по умолчанию для конкретного типа: 0 - для числовых, ''
(пустая строка) для текстовых/символьных и "нулевое" для дат. Например, 0000 для типа данных YEAR
или 0000-00-00 00:00:00
для типа DATETIME
.
Выражение col2 = DEFAULT
позволяет обновить значение записей на значения столбцов по умолчанию.
Если указана инструкция ORDER BY
, то строки обновляются в указанном порядке.
Инструкция LIMIT
устанавливает ограничение на количество строк, которые могут быть обновлены.
Для синтаксиса с несколькими таблицами UPDATE
обновляет строки в каждой таблице, удовлетворяющей условиям объединения. Каждая совпадающая строка обновляется один раз, даже если она соответствует условиям несколько раз. Для синтаксиса с несколькими таблицами нельзя использовать ORDER BY
и LIMIT
.
WHERE
при обновление записей таблицы.Изменение всех записей таблицы требуется очень редко. Гораздо чаще необходимо обновление значений для какой-то конкретной записи или для нескольких.
При обновление конкретной записи в таблице обычно используется ее уникальный индекс id
, который может автоматически формироваться в столбце AUTO_INCREMENT
при добавлении записи INSERT
.
В одном запросе можно обновить сразу несколько столбцов.
UPDATE books SET price=300, quantity=3 WHERE id = 101;
Для обновления нескольких строк/записей в таблице достаточно изменить условие в операторе WHERE
. Следующий запрос установит цену для книг, которых в наличии осталось меньше 5 штук.
UPDATE books SET price=300 WHERE quantity < 5;
Дополнительно смотрите материал "Использование инструкции WHERE в запросах к БД MySQL"
expr
.При обновлении можно задавать столбцу не только статичное значение, но и выражение. Выражением может быть как любое арифметическое выражение так и встроенная или хранимая функция MySQL. Допустим, в магазине проходит акция, и на книги русских писателей объявлена скидка в 15%:
UPDATE books SET price=price * 0.85 WHERE author_id IN (SELECT id FROM authors WHERE country = 'rus');
Обратите внимание, что обновление значений столбца происходит последовательно и в определенном порядке: слева направо. Например, следующий запрос сначала увеличит значение столбца quantity
на 1, а потом удвоит его для записи с id = 101
:
UPDATE books SET quantity=(quantity+1), quantity=(quantity*2) WHERE id = 101;
DEFAULT
.Можно изменить значение записей на значения столбцов по умолчанию DEFAULT
, которые задаются при создании таблицы. Например, заменим значение столбца quantity
на значение DEFAULT
для записи id = 101
.
UPDATE books SET quantity=DEFAULT WHERE id = 101;
LIMIT
при обновлении данных.Используя оператор LIMIT
, можно ограничить количество записей, которые должны быть обновлены. Например, обновим цену только у первых 3-х книг, прибывших на склад сегодня:
UPDATE books SET price=100 WHERE delivery_date=NOW() LIMIT 3;
Следует отметить, что LIMIT n
вовсе не означает, что обновятся n
строк. В результате запроса произойдёт обработка первых n
строк, подходящих под условие WHERE
.
ORDER BY
при обновлении данных.Если оператор UPDATE
включает предложение ORDER BY
, то строки обновляются в порядке, указанном в предложении. Это может быть полезно в определенных ситуациях, которые в противном случае могут привести к ошибке. Предположим, что таблица tbl
содержит идентификатор столбца с уникальным индексом id
. Следующая инструкция может завершиться ошибкой дублирования ключа, в зависимости от порядка обновления строк:
UPDATE tbl SET id = id + 1;
Например, если таблица содержит 1 и 2 в столбце идентификатора и 1 обновляется до 2, то до того, как 2 обновляется до 3, возникает ошибка. Чтобы избежать этой проблемы, добавим инструкцию ORDER BY
, чтобы строки с большими значениями идентификатора обновлялись раньше строк с меньшими значениями:
UPDATE tbl SET id = id + 1 ORDER BY id DESC;
БД MySQL позволяет обновлять данные сразу нескольких таблицах. Допустим, необходимо исправить ошибку в названии автора книги, и тут же обновить стоимость его книг:
UPDATE books AS b, authors AS a SET b.price=(b.price+100), a.name = 'А. Н. Толстой' WHERE b.author_id=a.id AND a.id=3;
LEFT JOIN
.Например обновим названия всех книг, добавив к названию, ее автора:
UPDATE author a LEFT JOIN books b ON b.author_id = a.id; SET b.title = CONCAT(b.title, ' (', a.name,')') WHERE b.title IS NOT NULL
или например, магазин продал в течении дня какие-то книги и необходимо внести коррективы в оставшееся количество:
UPDATE books b LEFT JOIN sales s ON b.book_id = s.book_id SET b.quantity = b.quantity - s.count; WHERE s.date_sale = NOW() AND s.count > 0;
Подробнее об объединениях таблиц смотрите в материале "Объединение таблиц LEFT|INNER JOIN в БД MySQL".
IF
и CASE
при обновлении данных.Иногда значение в операторе SET
может быть не явным, а зависеть от какого-либо условия. Например, необходимо уменьшить цены на все книги определенного автора на 100 р. Смотрим как это можно сделать с помощью условного оператора IF()
:
UPDATE books SET price = IF(author_id=3, price-100, price);
Усложним пример. Теперь нужно уменьшить цены на книги автора с id=1
на 50, на книги автора с id=2
поднять на 100, а книги автора с id=3
- уменьшить на 15%. Для этого лучше подойдет условный оператор CASE
. В нем можно перечислить сразу несколько условий:
UPDATE books SET price = CASE WHEN author_id = 1 THEN price-50 WHEN author_id = 2 THEN price+100 WHEN author_id = 3 THEN price*0.85 END;
Подробнее об использовании IF
и CASE
смотрите в материале "CASE
и IF()
в запросах БД MySQL"