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.21.8, «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;

Например, если таблица содержит 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.21.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;

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