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

Оператор UPDATE может начинаться с WITH для определения общих табличных выражений, доступных в рамках оператора UPDATE. См. Раздел 15.2.20, “WITH (Общие табличные выражения)”.

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

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 или нет.

Дополнительную информацию и примеры см. в Разделе 26.5, “Выбор партиций”.

where_condition — это выражение, которое должно быть истинным для каждой строки, подлежащей обновлению. Синтаксис выражений см. в Разделе 11.5, “Выражения”.

table_references и where_condition задаются, как описано в Разделе 15.2.13, “Оператор SELECT”.

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

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

Операторы UPDATE IGNORE, в том числе с оператором ORDER BY, помечены как небезопасные для репликации на основе операторов. (Это происходит из-за того, что порядок обновления строк определяет, какие строки игнорируются.) Такие операторы генерируют предупреждение в журнале ошибок при использовании режима репликации на основе операторов и записываются в бинарный журнал в формате на основе строк при использовании режима MIXED. (Ошибка #11758262, Ошибка #50439) Дополнительную информацию см. в Разделе 19.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 для числовых типов, пустая строка ('') для строковых типов и нулевое значение для типов даты и времени. См. Раздел 13.6, “Значения по умолчанию типов данных”.

Если явно обновляется сгенерированный столбец, единственное разрешенное значение — DEFAULT. Подробности о сгенерированных столбцах см. в Разделе 15.1.20.8, “CREATE TABLE и сгенерированные столбцы”.

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

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

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

UPDATE t SET id = id + 1;

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

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

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

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

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

Если вы используете оператор UPDATE с несколькими таблицами, затрагивающими InnoDB таблицы, для которых существуют внешние ключи, оптимизатор MySQL может обработать таблицы в порядке, отличающемся от порядка родительско-дочерних отношений. В этом случае оператор завершается ошибкой и откатом. Вместо этого обновите одну таблицу и воспользуйтесь возможностями ON UPDATE, предоставляемыми InnoDB, чтобы вызвать соответствующие изменения в других таблицах. См. Раздел 15.1.20.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;

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