Сообщить об ошибке.

MySQL: UPDATE обновление данных таблицы

Для синтаксиса с одной таблицей, оператор UPDATE обновляет столбцы существующих строк новыми значениями. Предложение SET указывает, какие столбцы следует изменить, и значения, которые им следует присвоить. Каждое значение может быть задано в виде выражения или ключевого слова DEFAULT. Оператор WHERE, если он задан, указывает условия, определяющие, какие строки следует обновить.

Для синтаксиса с несколькими таблицами UPDATE обновляет строки в каждой таблице, удовлетворяющей условиям объединения. Каждая совпадающая строка обновляется один раз, даже если она соответствует условиям несколько раз. Для синтаксиса с несколькими таблицами нельзя использовать ORDER BY и LIMIT.

Содержание:


Синтаксис инструкции 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:

  1. Условия в необязательном операторе WHERE определяют, какие строки следует обновить. ВНИМАНИЕ! Если при использовании инструкции UPDATE не использовать задающий условия оператор WHERE condition, то будут обновлены все строки в таблице tbl_name.

  2. Если присвоить значение NULL столбцу, при создании которого было указано NOT NULL, то запрос вернет ошибку. Если при этом указать параметр IGNORE, то значение будет изменено на значение по умолчанию для конкретного типа: 0 - для числовых, '' (пустая строка) для текстовых/символьных и "нулевое" для дат. Например, 0000 для типа данных YEAR или 0000-00-00 00:00:00 для типа DATETIME.

  3. Выражение col2 = DEFAULT позволяет обновить значение записей на значения столбцов по умолчанию.

  4. Если указана инструкция ORDER BY, то строки обновляются в указанном порядке.

  5. Инструкция LIMIT устанавливает ограничение на количество строк, которые могут быть обновлены.

  6. Для синтаксиса с несколькими таблицами 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"