Оператор UPDATE

Оператор UPDATE — это оператор языка SQL (Structured Query Language), предназначенный для изменения существующих данных в одной или нескольких строках таблицы реляционной базы данных. Оператор относится к группе DML (Data Manipulation Language — язык манипулирования данными) и позволяет модифицировать значения одного или нескольких столбцов в записях, удовлетворяющих заданному условию. Без указания условия оператор применяет изменения ко всем строкам таблицы.

Синтаксис

Базовый синтаксис оператора UPDATE в стандарте SQL и большинстве реализаций (MySQL, PostgreSQL, Microsoft SQL Server, SQLite, Oracle) выглядит следующим образом:

``sql UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition]; ``

• table_name — имя таблицы, в которой производится изменение.
• SET — ключевое слово, после которого указываются присваивания: столбец = новое значение.
• WHERE condition — необязательное условие, определяющее, какие строки будут обновлены. Если условие опущено, обновляются все строки таблицы.

Расширенный синтаксис (с подзапросами и JOIN)

В некоторых СУБД (например, PostgreSQL, SQL Server) поддерживается обновление с использованием соединений (JOIN) или подзапросов для получения значений из других таблиц:

``sql UPDATE table1 SET column1 = (SELECT expression FROM table2 WHERE condition) FROM table2 WHERE table1.id = table2.id; ``

В MySQL и MariaDB используется синтаксис с JOIN напрямую:

``sql UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.column1 = table2.column2 WHERE condition; ``

Поведение и особенности

Обновление всех строк

Если условие WHERE отсутствует, оператор UPDATE применяется ко всем записям таблицы. Например, следующий запрос установит значение столбца status в 'active' для всех строк таблицы users:

``sql UPDATE users SET status = 'active'; ``

Обновление нескольких столбцов

За один вызов оператора можно изменить значения нескольких столбцов, перечислив их через запятую:

``sql UPDATE products SET price = price * 1.1, updated_at = CURRENT_TIMESTAMP WHERE category_id = 5; ``

Использование выражений и функций

Новое значение может быть результатом арифметического выражения, вызова функции или подзапроса. Например, увеличение цены на 10%:

``sql UPDATE products SET price = price * 1.1; ``

Обновление с подзапросами

Значение для столбца может быть получено из другой таблицы. Пример: обновление суммы заказа на основе суммы его позиций.

``sql UPDATE orders SET total_amount = (SELECT SUM(amount) FROM order_items WHERE order_id = orders.id) WHERE status = 'pending'; ``

Транзакционность и целостность

Оператор UPDATE является транзакционным: в базах данных, поддерживающих транзакции (например, PostgreSQL, MySQL с InnoDB, SQL Server), изменения можно откатить командой ROLLBACK до момента фиксации (COMMIT). Это обеспечивает атомарность — либо все изменения применяются, либо ни одно.

При обновлении строк могут срабатывать триггеры (если они определены на таблице), а также проверяться ограничения целостности:

• NOT NULL — если столбец не допускает NULL, а новое значение — NULL, операция завершится ошибкой.
• PRIMARY KEY или UNIQUE — попытка установить дублирующееся значение в уникальном столбце вызовет ошибку.
• FOREIGN KEY — если обновляемое значение является внешним ключом, оно должно существовать в родительской таблице (или быть NULL, если допускается).

Производительность и индексы

При выполнении UPDATE с условием WHERE база данных использует индексы для быстрого поиска строк, подлежащих изменению. Если условие не покрыто индексом, может выполняться полное сканирование таблицы, что снижает производительность на больших объёмах данных.

Обновление индексированных столбцов приводит к перестроению индексов, что также влияет на скорость. В некоторых СУБД (например, в SQL Server) массовые обновления могут быть оптимизированы за счёт минимального логирования при определённых настройках.

Безопасность и ограничения

Права доступа

Для выполнения оператора UPDATE пользователь должен иметь соответствующие права на таблицу (обычно UPDATE). В некоторых СУБД (например, PostgreSQL) можно предоставить право на обновление только определённых столбцов.

Предотвращение массовых ошибок

Ошибка в условии WHERE может привести к нежелательному изменению большого количества записей. Рекомендуется:

• Перед выполнением UPDATE выполнить SELECT с тем же условием, чтобы проверить, какие строки будут затронуты.
• Использовать транзакции: выполнить UPDATE, проверить результат, затем выполнить COMMIT или ROLLBACK.
• В средах разработки и тестирования применять ограничения (например, LIMIT в MySQL, хотя это нестандартная возможность).

Блокировки

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

Примеры использования

Простое обновление

``sql UPDATE employees SET salary = 60000 WHERE employee_id = 123; ``

Обновление с вычислением

``sql UPDATE accounts SET balance = balance - 100 WHERE account_id = 456 AND balance >= 100; ``

Обновление из другой таблицы (PostgreSQL)

``sql UPDATE products SET price = categories.base_price * 1.2 FROM categories WHERE products.category_id = categories.id AND categories.name = 'Electronics'; ``

Обновление с использованием CASE

``sql UPDATE orders SET status = CASE WHEN total_amount > 1000 THEN 'premium' ELSE 'standard' END WHERE created_at > '2024-01-01'; ``

Отличия от других операторов DML

• INSERT — добавляет новые строки, не изменяет существующие.
• DELETE — удаляет строки, не изменяет их содержимое.
• MERGE (или UPSERT) — объединяет операции INSERT, UPDATE и DELETE в один оператор, позволяя обновлять существующие строки и вставлять новые в зависимости от условия.

Поддержка в различных СУБД

Все основные реляционные СУБД поддерживают оператор UPDATE в соответствии со стандартом SQL. Однако существуют диалектные различия:

• MySQL / MariaDB — поддерживают синтаксис UPDATE с JOIN, а также модификаторы LOW_PRIORITY, IGNORE, ORDER BY и LIMIT.
• PostgreSQL — поддерживает обновление с использованием FROM и RETURNING (возврат изменённых строк).
• Microsoft SQL Server — поддерживает FROM и OUTPUT (аналог RETURNING), а также обновление через табличные подсказки (table hints).
• Oracle — поддерживает UPDATE ... FROM через подзапросы, а также RETURNING INTO для получения старых и новых значений.

Источники

• ISO/IEC 9075-2:2016 — Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
• Документация PostgreSQL: UPDATE.
• Документация MySQL: UPDATE Statement.
• Документация Microsoft SQL Server: UPDATE (Transact-SQL).
• Документация Oracle Database: UPDATE.