Оператор UPDATE — это оператор DML, который изменяет строки в таблице.

Синтаксис для одной таблицы:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Синтаксис для нескольких таблиц:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

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

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

Для разнесённых таблиц оба синтаксиса оператора (с одной и с несколькими таблицами) поддерживают использование оператора PARTITION в качестве части ссылки на таблицу. Этот оператор принимает список одной или нескольких партиций или подпартиций (или и то, и другое). Проверяются только указанные партиции (или подпартиции). Строка, которая не находится ни в одной из этих партиций или подпартиций, не обновляется, независимо от того, удовлетворяет ли она условию where_condition.

Дополнительную информацию и примеры см. в разделе 22.5, «Выбор партиций».

where_condition — это выражение, которое вычисляется как истинное для каждой строки, подлежащей обновлению. Синтаксис выражений см. в разделе 9.5, «Выражения».

table_references и where_condition задаются как описано в разделе 13.2.9, «Оператор SELECT».

Для столбцов, которые фактически обновляются в операторе UPDATE, требуется привилегия UPDATE. Для столбцов, которые только считываются, но не изменяются, достаточно привилегии SELECT.

Оператор UPDATE поддерживает следующие модификаторы:

Операторы UPDATE IGNORE, включая те, у которых есть оператор ORDER BY, считаются небезопасными для репликации на основе операторов. (Это связано с тем, что порядок обновления строк определяет, какие строки игнорируются.) Такие операторы генерируют предупреждение в журнале ошибок при использовании режима репликации на основе операторов и записываются в бинарный журнал с помощью формата на основе строк при использовании режима MIXED. (Ошибка #11758262, ошибка #50439) Дополнительную информацию см. в разделе 16.2.1.3, «Определение безопасных и небезопасных операторов в журналировании двоичных данных».

Если вы обращаетесь к столбцу обновляемой таблицы в выражении, оператор UPDATE использует текущее значение столбца. Например, следующий оператор присваивает col1 значение, на единицу больше его текущего значения:

UPDATE t1 SET col1 = col1 + 1;

Второе присваивание в следующем операторе присваивает col2 текущее (обновлённое) значение col1, а не исходное значение col1. В результате col1 и col2 будут иметь одинаковое значение. Это отличается от стандартного SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Присваивания в операторе UPDATE с одной таблицей обычно выполняются слева направо. При обновлении нескольких таблиц нет гарантии, что присваивания выполняются в определённом порядке.

Если вы устанавливаете столбец в значение, которое он уже имеет, MySQL это распознаёт и не обновляет его.

Если вы обновляете столбец, который был объявлен NOT NULL, задавая ему значение NULL, возникает ошибка, если включён строгий режим SQL; в противном случае столбец устанавливается в неявное значение по умолчанию для типа данных столбца, и счётчик предупреждений увеличивается. Неявное значение по умолчанию — 0 для числовых типов, пустая строка ('') для строковых типов и значение «ноль» для типов даты и времени. См. раздел 11.6, «Значения по умолчанию типов данных».

Если явно обновляется сгенерированный столбец, единственное допустимое значение — DEFAULT. Дополнительную информацию о сгенерированных столбцах см. в разделе 13.1.18.7, «CREATE TABLE и сгенерированные столбцы».

Оператор UPDATE возвращает количество строк, которые были фактически изменены. Функция API C возвращает количество строк, которые соответствовали условиям и были обновлены, и количество предупреждений, произошедших во время выполнения оператора UPDATE.

Вы можете использовать LIMIT row_count для ограничения области действия оператора UPDATE. Оператор LIMIT — это ограничение по количеству подходящих строк. Оператор завершается, как только найдено row_count строк, удовлетворяющих условию WHERE, независимо от того, были ли они изменены.

Если оператор UPDATE содержит оператор ORDER BY, строки обновляются в порядке, указанном в операторе. Это может быть полезно в определённых ситуациях, которые в противном случае могут привести к ошибке. Предположим, что таблица t содержит столбец id с уникальным индексом. Следующий оператор может завершиться с ошибкой дублирования ключа в зависимости от порядка обновления строк:

UPDATE t SET id = id + 1;

Например, если в таблице в столбце id находятся значения 1 и 2, и значение 1 обновляется до 2 до того, как значение 2 обновится до 3, возникает ошибка. Чтобы избежать этой проблемы, добавьте оператор ORDER BY, чтобы обновлять строки с большими значениями id перед строками с меньшими значениями:

UPDATE t SET id = id + 1 ORDER BY id DESC;

Вы также можете выполнять операции UPDATE над несколькими таблицами. Однако вы не можете использовать ORDER BY или LIMIT с оператором UPDATE для нескольких таблиц. Оператор table_references перечисляет таблицы, участвующие в объединении. Его синтаксис описан в разделе 13.2.9.2, «Оператор JOIN». Вот пример:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

В предыдущем примере показано внутреннее объединение, использующее оператор запятой, но операторы UPDATE с несколькими таблицами могут использовать любые типы объединения, разрешённые в операторах SELECT, такие как LEFT JOIN.

Если вы используете оператор UPDATE с несколькими таблицами, связанными с ограничениями внешних ключей, оптимизатор MySQL может обрабатывать таблицы в порядке, отличном от порядка родительско-дочерних связей. В этом случае оператор завершается ошибкой и отменяется. Вместо этого обновите одну таблицу и воспользуйтесь возможностями ON UPDATE, предоставляемыми InnoDB, для изменения других таблиц соответствующим образом. См. раздел 13.1.18.5, «Ограничения внешнего ключа».

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

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

Чтобы снизить розничную цену любых товаров, для которых наценка составляет 30% или больше и количество которых на складе меньше ста, можно попробовать использовать оператор UPDATE, такой как следующий, который использует подзапрос в условии WHERE. Как показано здесь, это утверждение не работает:

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

Вместо этого можно использовать обновление нескольких таблиц, в котором подзапрос перемещается в список таблиц, которые нужно обновить, используя псевдоним для ссылки на него в самом внешнем условии WHERE, например так:

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

Поскольку оптимизатор по умолчанию пытается объединить производную таблицу discounted в самый внешний блок запроса, это работает только если вы принудительно выполните материализацию производной таблицы. Это можно сделать, установив флаг derived_merge системной переменной optimizer_switch на значение off перед выполнением обновления или с помощью подсказки оптимизатора NO_MERGE, как показано здесь:

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

Преимущество использования подсказки оптимизатора в данном случае заключается в том, что она применяется только внутри блока запроса, где она используется, поэтому нет необходимости изменять значение optimizer_switch снова после выполнения UPDATE.

Другой вариант — переписать подзапрос так, чтобы он не использовал IN или EXISTS, например так:

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

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