UPDATE: примеры (SQL)

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

Обновление одного поля для всех строк таблицы.

UPDATE Products
SET Price = Price * 1.1;

(Выводится количество измененных строк, например: (5 rows affected))

Обновление нескольких полей с условием WHERE.

UPDATE Employees
SET Salary = Salary * 1.05,
    LastReviewDate = GETDATE()
WHERE DepartmentID = 3;

Использование предложения FROM с JOIN для обновления на основе данных другой таблицы.

UPDATE p
SET p.Stock = p.Stock + s.DeliveryQty
FROM Products p
INNER JOIN Shipments s ON p.ProductID = s.ProductID
WHERE s.ShipmentDate = '2023-11-15';

Обновление с предложением OUTPUT для возврата старых и новых значений.

UPDATE Customers
SET LoyaltyPoints = LoyaltyPoints + 100
OUTPUT 
    inserted.CustomerID,
    deleted.LoyaltyPoints AS OldPoints,
    inserted.LoyaltyPoints AS NewPoints
WHERE CustomerID = 10;

CustomerID  OldPoints  NewPoints
----------  ---------  ---------
10          550        650

Использование TOP для ограничения числа обновляемых строк.

UPDATE TOP (10) Orders
SET Status = 'Processed'
WHERE Status = 'New';

Похожие средства в MS SQL

MERGE (UPSERT) выполняет операции вставки, обновления и удаления за один вызов. Функция удобна для синхронизации двух таблиц, когда необходимо обновить существующие записи и добавить новые.

Обновление через CTE (Common Table Expression) позволяет обновлять данные, используя результат сложного запроса с временным именем. Подход повышает читаемость, когда обновление основывается на многоступенчатом вычислении.

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

Метод .WRITE в предложении SET предназначен для выполнения частичных обновлений больших строковых или бинарных данных, что эффективнее полной замены значения.

Реализация UPDATE в других СУБД

MySQL поддерживает схожий синтаксис UPDATE с предложением JOIN. Отличие заключается в отсутствии предложения OUTPUT и использовании ключевого слова LIMIT вместо TOP.

UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.1
WHERE d.name = 'Sales'
LIMIT 10;

Oracle использует UPDATE с возможностью применения RETURNING INTO для получения обновленных значений, аналогично OUTPUT в SQL Server. Также поддерживается коррелированный подзапрос в SET.

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20
RETURNING employee_id, salary INTO :id, :new_sal;

PostgreSQL предлагает синтаксис, близкий к стандарту SQL, с предложением FROM и возможностью RETURNING данных. PostgreSQL позволяет использовать JOIN в FROM аналогично MS SQL.

UPDATE accounts
SET balance = balance + transactions.amount
FROM transactions
WHERE accounts.id = transactions.account_id
RETURNING accounts.id, accounts.balance;

SQLite имеет ограниченную поддержку UPDATE: допустимо обновление одной таблицы с использованием подзапросов в SET или WHERE. Предложение FROM с JOIN появилось в более поздних версиях.

UPDATE orders
SET status = 'shipped'
WHERE id IN (SELECT order_id FROM shipments WHERE shipped_date IS NOT NULL);

Sybase ASE использует синтаксис, исторически близкий к Transact-SQL, но без некоторых современных функций, таких как OUTPUT.

Типичные ошибки при работе с UPDATE

Отсутствие условия WHERE приводит к обновлению всех строк в таблице, что часто является ошибкой данных.

UPDATE Products SET IsActive = 0;
-- Все продукты станут неактивными

Некорректное соединение таблиц в предложении FROM может вызвать непреднамеренное обновление не тех строк или синтаксическую ошибку.

UPDATE t1
SET t1.Value = t2.Value
FROM Table1 t1, Table2 t2; -- Декартово произведение без условия

Попытка обновления несуществующего столбца или нарушение ограничений целостности (CHECK, FOREIGN KEY, NOT NULL) приводит к откату транзакции.

UPDATE Employees SET Phone = NULL;
-- Ошибка, если на столбце Phone установлено ограничение NOT NULL

Использование агрегатных функций напрямую в SET без подзапроса вызывает ошибку.

UPDATE Orders SET TotalAmount = SUM(LineAmount); -- Ошибка
-- Правильно:
UPDATE Orders o
SET TotalAmount = (SELECT SUM(LineAmount) FROM OrderLines ol WHERE ol.OrderID = o.OrderID);

Изменения в операторе UPDATE в новых версиях MS SQL

В SQL Server 2008 появилась возможность использовать переменные табличного типа в предложении FROM оператора UPDATE, что упрощает массовые обновления на основе параметризованных наборов данных.

Начиная с SQL Server 2005, оператор UPDATE может применяться вместе с обобщенным табличным выражением (CTE), что позволяет создавать более структурированные и читаемые запросы для сложных обновлений.

В SQL Server 2016 улучшена обработка операций с системой управления версиями темпоральных таблиц при выполнении UPDATE. Обновления в темпоральных таблицах автоматически фиксируют исторические состояния строк.

Постоянно оптимизируется производительность и параллельное выполнение операций UPDATE, особенно при работе с кластеризованными индексами columnstore и таблицами, находящимися в памяти (In-Memory OLTP).

Расширенные сценарии применения UPDATE

Обновление с использованием обобщенного табличного выражения (CTE) для задания сложной логики.

WITH RankedEmployees AS (
    SELECT EmployeeID, Salary,
           ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY HireDate) AS rn
    FROM Employees
)
UPDATE RankedEmployees
SET Salary = Salary * 1.15
WHERE rn = 1; -- Повышение зарплаты самому старому сотруднику в каждом отделе

Обновление данных с применением метода .WRITE для частичной модификации больших строк.

UPDATE ProductDescriptions
SET Description.WRITE(N' (новинка!)', LEN(Description), 0)
WHERE ProductID = 1001;
-- Добавляет текст в конец существующего описания

Использование UPDATE с выходными данными для логирования изменений.

DECLARE @ChangeLog TABLE (ProductID int, OldPrice money, NewPrice money, ChangeDate datetime);

UPDATE Products
SET Price = Price * 0.9 -- Скидка 10%
OUTPUT inserted.ProductID, deleted.Price, inserted.Price, GETDATE()
INTO @ChangeLog
WHERE CategoryID = 5;

SELECT * FROM @ChangeLog;

Обновление на основе результата оконной функции, например, для каскадного изменения рангов.

UPDATE SalesData
SET DailyRank = rnk
FROM (
    SELECT SaleID,
           RANK() OVER (PARTITION BY SaleDate ORDER BY Amount DESC) AS rnk
    FROM SalesData
) AS ranked
WHERE SalesData.SaleID = ranked.SaleID;

Обновление через переменную табличного типа для пакетной обработки.

DECLARE @Updates AS TABLE (ID int, NewValue varchar(50));
INSERT INTO @Updates VALUES (1, 'Активно'), (2, 'Приостановлено');

UPDATE t
SET t.Status = u.NewValue
FROM TargetTable t
INNER JOIN @Updates u ON t.ID = u.ID;