UPDATE (Transact-SQL)

Применимо к:SQL Server База данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse Analytics Система платформы аналитики (PDW)Хранилище в Microsoft Fabric База данных SQL в Microsoft Fabric

Изменяет существующие данные в таблице или представлении в SQL Server. Примеры см. в разделе Примеры.

Соглашения о синтаксисе Transact-SQL

Syntax

-- Syntax for SQL Server and Azure SQL Database  

[ WITH <common_table_expression> [...n] ]  
UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object> | rowset_function_limited   
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
      }  
      | @table_variable      
    }  
    SET  
        { column_name = { expression | DEFAULT | NULL }  
          | { udt_column_name.{ { property_name = expression  
                                | field_name = expression }  
                                | method_name ( argument [ ,...n ] )  
                              }  
          }  
          | column_name { .WRITE ( expression , @Offset , @Length ) }  
          | @variable = expression  
          | @variable = column = expression  
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
        } [ ,...n ]   
  
    [ <OUTPUT Clause> ]  
    [ FROM{ <table_source> } [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                  { { [ GLOBAL ] cursor_name }   
                      | cursor_variable_name   
                  }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]  
[ ; ]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}

-- Syntax for Azure Synapse Analytics and Microsoft Fabric

[ WITH <common_table_expression> [ ,...n ] ]
UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name
SET { column_name = { expression | NULL } } [ ,...n ]  
FROM [ database_name . [ schema_name ] . | schema_name . ] table_name   
JOIN {<join_table_source>}[ ,...n ] 
ON <join_condition>
[ WHERE <search_condition> ]   
[ OPTION ( LABEL = label_name ) ]  
[;]  

<join_table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias 
    [ <tablesample_clause>]  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
}

-- Syntax for Parallel Data Warehouse

UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name   
SET { column_name = { expression | NULL } } [ ,...n ]  
[ FROM from_clause ]  
[ WHERE <search_condition> ]   
[ OPTION ( LABEL = label_name ) ]  
[;]

Arguments

С common_table_expression <>
Задаёт временное названное множество результатов или представление, также известное как выражение общей таблицы (CTE), определённое в рамках этого UPDATE оператора. Набор результатов CTE выводится из простого запроса и ссылается на UPDATE операторы.

Общие табличные выражения также могут использоваться с операторами SELECT, INSERT, DELETE, и CREATE VIEW . Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

TOP (выражение) [ PERCENT ]
Задает число или процент обновляемых строк. expression может быть либо числом, либо процентом от числа строк.

Строки, упомянутые в выражении TOP, используются с INSERT, UPDATEили DELETE не расположены в каком-либо порядке.

В , в , и в операторах требуются скобки, разделяющие выражение в TOPINSERTUPDATEDELETE. Дополнительные сведения см. в разделе TOP (Transact-SQL).

table_alias
Псевдоним, указанный в UPDATE клаузе, представляющем таблицу или представление, из которого строки должны обновляться.

server_name
Имя сервера (с использованием имени связанного сервера или функции OPENDATASOURCE в качестве имени сервера), на котором расположена таблица или представление. Если указано server_name, необходимо указать database_name и schema_name.

database_name
Имя базы данных.

schema_name
Имя схемы, которой принадлежит таблица или представление.

table_or_view_name
Имя таблицы или представления, из которых должны обновляться строки. Представление, на которое ссылается аргумент table_or_view_name, должно быть обновляемым и ссылаться только на одну базовую таблицу в предложении FROM в представлении. Для получения дополнительной информации об обновляемых видах см. CREATE VIEW (Transact-SQL).

rowset_function_limited
Функция OPENQUERY или OPENROWSET, в зависимости от возможностей поставщика.

С (<>
Задает одно или несколько табличных указаний, разрешенных для целевой таблицы. Ключевое слово WITH и круглые скобки обязательны. NOLOCK, READUNCOMMITTED, NOEXPAND и несколько других не допускаются. Сведения о табличных указаниях см. в разделе Табличные указания (Transact-SQL).

@ table_variable
Задает переменную table в качестве источника таблицы.

SET
Задает список обновляемых имен столбцов или переменных.

column_name
Столбец, содержащий изменяемые данные. Аргумент column_name должен существовать в table_or view_name. Столбцы удостоверений не могут быть обновлены.

expression
Переменная, литеральное значение, выражение или инструкция подзапроса выборки (заключенная в скобки), которые возвращают единственное значение. Значение, возвращаемое expression, заменяет существующее значение в column_name или @variable.

Note

При ссылке на типы данных символов Юникода nchar, nvarchar и ntext выражение 'expression' должно начинаться с заглавной буквы 'N'. Если значение "N" не указано, SQL Server преобразует строку в кодовую страницу, соответствующую параметрам сортировки по умолчанию базы данных или столбца. Любые символы, не входящие в эту кодовую страницу, будут утрачены.

DEFAULT
Указывает, что существующее в столбце значение будет заменено значением по умолчанию, определенным для данного столбца. Также может использоваться для присвоения значения NULL, если столбец не имеет значений по умолчанию и может принимать значения NULL.

{ += | -= | *= | /= | %= | &= } | ^= | |=
Составной оператор присваивания:
+= Сложение и присваивание
-= Вычитание и присваивание
*= Умножение и присваивание
/= Деление и присваивание
%= Остаток от деления и присваивание
&= Выполнение побитовой операции AND и присваивание
^= Выполнение побитовой операции XOR и присваивание
|= Выполнение побитовой операции OR и присваивание

udt_column_name
Столбец определяемого пользователем типа.

property_name | field_name
Общедоступное свойство или общедоступный элемент данных определяемого пользоватлем типа.

method_name(аргумент [ ,... n] )
Не статичный метод общего мутатора udt_column_name, принимающий один или несколько аргументов.

. WRITE (выражение,@Смещение,@длина)
Указывает, что часть значения column_name будет изменена. expression заменяет единицы @Length начиная с @Offset в column_name. В этом предложении можно указать только столбцы типа varchar(max), nvarchar(max) или varbinary(max). column_name не может иметь значение NULL и не может быть квалифицирован с именем таблицы или псевдонимом таблицы.

expression является значением, которое копируется в column_name. Аргумент expression должен преобразовываться или поддерживать неявное преобразование к типу column_name. Если для expression установлено значение NULL, @Length не учитывается, а значение в column_name усекается с позиции, на которую указывает аргумент @Offset.

Аргумент @Offset — это начальная точка в значении, хранимом в column_name, начиная с которой записывается expression. @ Смещение — это порядковое положение от нуля, bigintи не может быть отрицательным числом. Если аргумент @Offset имеет значение NULL, операция обновления добавляет значение expression в конце существующего значения аргумента column_name, а аргумент @Length пропускается. Если значение @Offset больше длины байтов значения column_name, ядро СУБД возвращает ошибку. Если сумма значений @Offset и @Length превышает длину базового значения столбца, удаление выполняется до последнего символа этого значения.

Аргумент @Length задает длину части значения столбца начиная с @Offset, которая заменяется на выражение expression. @ длинаbigint и не может быть отрицательным числом. Если аргумент @Length имеет значение NULL, операция обновления удаляет все данные, начиная со значения @Offset до конца значения column_name.

Дополнительные сведения см. ниже в разделе Обновление типов данных большого объема.

@ переменная
Объявленная переменная, которой присваивается значение, возвращенное expression.

SET @ переменная = Колонка = выражение устанавливает переменную в то же значение, что и столбец. Это отличается от SET@выражения столбца и = столбца = переменных, которые устанавливают переменную на значение столбца до обновления.

<OUTPUT_Clause>
Возвращает обновлённые данные или выражения на их основе в рамках UPDATE операции. Предложение OUTPUT не поддерживается в каких-либо инструкциях DML, предназначенных для удаленных таблиц или представлений. Дополнительные сведения об аргументах и логике работы этого предложения см. в разделе Предложение OUTPUT (Transact-SQL).

От <table_source>
Определяет, что для определения критериев операции обновления используется таблица, представление или производная таблица. Дополнительные сведения см. в разделе FROM (Transact-SQL).

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

Вид с триггером ВМЕСТО OF UPDATE не может быть целью UPDATE с FROM clause.

Note

Любой вызов функции OPENDATASOURCE, OPENQUERY или OPENROWSET в предложении FROM вычисляется отдельно и независимо от любого вызова этих функций, используемого как назначение при обновлении, даже если в двух таких вызовах будут заданы идентичные аргументы. В частности, условия фильтра или соединения, применяемые к результатам одного из таких вызовов, никак не влияют на результаты другого.

WHERE
Задает условия, ограничивающие обновляемые строки. Существует два вида обновлений в зависимости от используемой формы предложения WHERE.

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

<search_condition>
Задает условие, которому должны удовлетворять обновляемые строки. Условие поиска может также представлять собой условие, на котором основано соединение. Количество предикатов, которое может содержать условие поиска, не ограничено. Дополнительные сведения об условиях поиска и предикатах см. в статье Условие поиска (Transact-SQL).

ТЕКУЩЕЕ
Определяет, что обновление выполняется в текущей позиции указанного курсора.

Позиционированное обновление с использованием предложения WHERE CURRENT OF обновляет единственную строку в текущем положении курсора. Такое обновление может быть более точным, чем поисковое обновление, в котором для выбора строк используется предложение WHERE <search_condition>. При поиске обновление изменяет несколько строк, если условие поиска не однозначно определяет одну строку.

GLOBAL
Указывает, что аргумент cursor_name ссылается на глобальный курсор.

cursor_name
Имя открытого курсора, из которого должна быть произведена выборка. Если существует как глобальный, так и локальный курсор с именем cursor_name, этот аргумент ссылается на глобальный курсор, если указан аргумент GLOBAL, в противном случае он ссылается на локальный курсор. Курсор должен позволять производить обновления.

cursor_variable_name
Имя переменной курсора. Аргумент cursor_variable_name должен содержать ссылку на курсор, обновления которого разрешены.

ВАРИАНТ (<query_hint> [ ,... n ] )
Указывает, что для настройки способа, которым компонент ядро СУБД обрабатывает инструкцию, используются подсказки оптимизатора. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Лучшие практики

Для возврата в клиентское приложение количества вставленных строк используйте функцию @@ROWCOUNT. Для получения дополнительной информации смотрите @@ROWCOUNT (Transact-SQL).

Имена переменных могут использоваться в UPDATE операторах для отображения старых и новых значений, но это следует применять только когда UPDATE оператор затрагивает одну запись. Если оператор UPDATE затрагивает несколько записей, чтобы вернуть старые и новые значения для каждой записи, используйте клаузу OUTPUT.

Проявляйте осторожность, указывая предложение FROM при задании критериев для операции обновления. Результаты UPDATE оператора не определены, если оператор содержит клаузу FROM, которая не задана таким образом, чтобы для каждого обновленного столбца было доступно только одно значение, то есть если утверждение UPDATE не является детерминированным. Например, в UPDATE операторе в следующем скрипте обе строки в Table1 соответствуют требованиям клаузы FROM в операторе UPDATE ; но не определено, какая строка из Table1 используется для обновления строки в Table2.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ('dbo.Table1', 'U') isn't NULL  
    DROP TABLE dbo.Table1;  
GO  
IF OBJECT_ID ('dbo.Table2', 'U') isn't NULL  
    DROP TABLE dbo.Table2;  
GO  
CREATE TABLE dbo.Table1   
    (ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
CREATE TABLE dbo.Table2   
    (ColA INT PRIMARY KEY NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0);  
INSERT INTO dbo.Table2 VALUES(1, 0.0);  
GO  
UPDATE dbo.Table2   
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB  
FROM dbo.Table2   
    INNER JOIN dbo.Table1   
    ON (dbo.Table2.ColA = dbo.Table1.ColA);  
GO  
SELECT ColA, ColB   
FROM dbo.Table2;

Та же проблема может возникнуть при объединении предложений FROM и WHERE CURRENT OF. В следующем примере обе строки в Table2 удовлетворяют условиям предложения FROM в инструкции UPDATE. Не определено, какая строка из Table2 должна использоваться для обновления строки в Table1.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ('dbo.Table1', 'U') isn't NULL  
    DROP TABLE dbo.Table1;  
GO  
IF OBJECT_ID ('dbo.Table2', 'U') isn't NULL  
    DROP TABLE dbo.Table2;  
GO  
CREATE TABLE dbo.Table1  
    (c1 INT PRIMARY KEY NOT NULL, c2 INT NOT NULL);  
GO  
CREATE TABLE dbo.Table2  
    (d1 INT PRIMARY KEY NOT NULL, d2 INT NOT NULL);  
GO  
INSERT INTO dbo.Table1 VALUES (1, 10);  
INSERT INTO dbo.Table2 VALUES (1, 20), (2, 30);  
GO  
DECLARE abc CURSOR LOCAL FOR  
    SELECT c1, c2   
    FROM dbo.Table1;  
OPEN abc;  
FETCH abc;  
UPDATE dbo.Table1   
SET c2 = c2 + d2   
FROM dbo.Table2   
WHERE CURRENT OF abc;  
GO  
SELECT c1, c2 FROM dbo.Table1;  
GO

Поддержка совместимости

Поддержка использования подсказок READUNCOMMITTED и NOLOCK в FROM clause, применяемых к целевой таблице оператора или UPDATEDELETE, будет удалена в будущей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

Типы данных

Все столбцы, имеющие тип данных char и nchar, дополняются справа до заданной длины.

Если ANSI_PADDING установлено в OFF, все сложные пробелы удаляются из данных, вставленных в столбцы varchar и nvarchar , за исключением строк, содержащих только пробелы. Эти строки усекаются до пустых строк. Если ANSI_PADDING установлено на ВКЛЮЧЕНО, вставляются промежуточные пространства. Драйвер Microsoft SQL Server ODBC и OLE DB Provider для SQL Server автоматически включают ANSI_PADDING ON для каждого соединения. Этот параметр можно настроить в источниках данных ODBC или устанавливая атрибуты или свойства соединений. Дополнительные сведения см. в разделе SET ANSI_PADDING (Transact-SQL).

Обновление столбцов типа text, ntext и image

Изменение текстового, ntext или столбца изображения инициализирует UPDATE столбец, присваивает ему действительный указатель текста и выделяет хотя бы одну страницу данных, если столбец не обновляется с помощью NULL.

Для замены или изменения больших блоков текстовых, ntext или изображений используйте WRITETEXT или UPDATETEXT вместо оператора UPDATE .

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

Important

Типы данных ntext, text и image будут удалены в следующей версии Microsoft SQL Server. Следует избегать использования этих типов данных при новой разработке и запланировать изменение приложений, использующих их в настоящий момент. Вместо них следует использовать типы данных nvarchar(max), varchar(max)и varbinary(max) .

Обновление типов данных больших значений

Используйте предложение .WRITE (expression,@Offset,@Length) для выполнения частичного или полного обновления типов данных varchar(max), nvarchar(max) и varbinary(max).

Например, частичное обновление столбца с типом varchar(max) может удалить или изменить только первые 200 байтов в столбце (200 символов при использовании символов ASCII), тогда как полное обновление удалит или изменит все данные в столбце. Обновления .WRITE, вставляющие или добавляющие новые данные, имеют минимальное протоколирование, если установлена простая модель восстановления базы данных или модель восстановления с неполным протоколированием. Минимальное ведение журнала не используется при обновлении существующих значений. Дополнительные сведения см. в статье Журнал транзакций (SQL Server).

ядро СУБД преобразует частичное обновление в полное, когда UPDATE оператор вызывает одно из следующих действий:

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

Нельзя использовать . Предложение WRITE для обновления столбца NULL или задания значения column_name значение NULL.

Параметры @Offset и @Length указываются в байтах для типов данных varbinary и varchar и в байтовых парах для типа данных nvarchar. Дополнительные сведения о длине строковых типов данных см. в разделах char и varchar (Transact-SQL) и nchar и nvarchar (Transact-SQL).

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

Если на столбец, измененный предложением .WRITE, ссылается предложение OUTPUT, полное значение столбца, либо исходный образ в deleted.column_name, либо преобразованный образ в inserted.column_name, возвращается определенному столбцу в табличной переменной. См. пример Т ниже.

Чтобы добиться функциональности предложения .WRITE при обработке других символьных или двоичных типов данных, используется STUFF (Transact-SQL).

Обновление столбцов определяемого пользователем типа

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

Предоставление значения в системном типе данных SQL Server, если определяемый пользователем тип поддерживает неявное или явное преобразование из этого типа. Следующий пример демонстрирует, как обновить значение в столбце определяемого пользователем типа Point путем явного преобразования строки.
```
UPDATE Cities  
SET Location = CONVERT(Point, '12.3:46.2')  
WHERE Name = 'Anchorage';  
```
Вызов метода, помеченного в качестве мутатора определенного пользователем типа для осуществления обновления. Следующий пример вызывает метод мутатора типа Point с именем SetXY. Это обновляет состояние экземпляра типа.
```
UPDATE Cities  
SET Location.SetXY(23.5, 23.5)  
WHERE Name = 'Anchorage';  
```
Note

SQL Server возвращает ошибку, если метод мутатора возвращает значение NULL языка Transact-SQL либо если новое значение, порожденное методом мутатора, соответствует значению NULL.
Изменение значения зарегистрированного свойства или общедоступного элемента данных определяемого пользователем типа. Выражение, предоставляющее значение, должно допускать неявное преобразование в тип свойства. В следующем примере изменяется значение свойства X определяемого пользователем типа Point.
```
UPDATE Cities  
SET Location.X = 23.5  
WHERE Name = 'Anchorage';  
```
Для изменения различных свойств одного пользовательского столбца типа можно выпустить несколько UPDATE операторов или вызвать мутаторный метод этого типа.

Обновление данных FILESTREAM

Вы можете использовать UPDATE этот оператор для обновления поля FILESTREAM до нулевого, пустого значения или относительно небольшого объёма встроенных данных. Однако при работе с большими объемами данных более эффективно передавать поток в файл с использованием интерфейсов Win32. При обновлении поля FILESTREAM происходит изменение базовых данных BLOB в файловой системе. Если в поле FILESTREAM содержится значение NULL, данные BLOB, связанные с этим полем, удаляются. Нельзя использовать. WRITE(), чтобы выполнить частичные обновления данных FILESTREAM. Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

Обработка ошибок

Если обновление строки нарушает ограничение, правило или установку NULL для столбца либо новое значение имеет несовместимый тип данных, то инструкция отменяется, возвращается ошибка и никакие записи не обновляются.

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

Если обновление столбца или столбцов, участвующих в кластеризованном индексе, приводит к тому, что размер кластеризованного индекса и строки превышает 8 060 байт, обновление заканчивается неудачей и возвращается сообщение об ошибке.

Interoperability

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

Когда INSTEAD OF триггер определяется на UPDATE действиях против таблицы, запускается триггер вместо UPDATE оператора. Ранние версии SQL Server поддерживали только триггеры AFTER, определённые на UPDATE on, и другие операторы модификации данных. Клаузу FROM нельзя задать в UPDATE операторе, который ссылается прямо или косвенно на представление с определённым INSTEAD OF триггером. Для получения дополнительной информации о триггерах ВМЕСТО ОФ см. CREATE TRIGGER (Transact-SQL).

В настоящее время пункт FROM нельзя указать в UPDATE заявлении на Warehouse в Microsoft Fabric. Поддерживаются операторы, основанные на одной таблице UPDATE .

ограничения

Клауза FROM нельзя указать в UPDATE операторе, который ссылается прямо или косвенно на представление с определённым INSTEAD OF триггером. Для получения дополнительной информации о INSTEAD OF триггерах см. CREATE TRIGGER (Transact-SQL).

Когда цель UPDATE оператора является выражением общей таблицы (CTE), все ссылки на CTE в этом утверждении должны совпадать. Например, если для обобщенного табличного выражения в предложении FROM назначается псевдоним, то этот псевдоним должен использоваться для всех остальных ссылок на обобщенное табличное выражение. Неявные ссылки на CTE требуются, так как CTE не имеет идентификатора объекта, который SQL Server использует для распознавания неявной связи между объектом и его псевдонимом. В отсутствие такой связи план запроса может непредвиденным образом построить работу с соединениями, что приведет к нежелательным результатам запроса. В следующих примерах показаны правильные и неправильные методы задания обобщенного табличного выражения, когда оно является целевым объектом операции обновления.

USE tempdb;  
GO  
-- UPDATE statement with CTE references that are correctly matched.  
DECLARE @x TABLE (ID INT, Value INT);  
DECLARE @y TABLE (ID INT, Value INT);  
INSERT @x VALUES (1, 10), (2, 20);  
INSERT @y VALUES (1, 100),(2, 200);  
  
WITH cte AS (SELECT * FROM @x)  
UPDATE x -- cte is referenced by the alias.  
SET Value = y.Value  
FROM cte AS x  -- cte is assigned an alias.  
INNER JOIN @y AS y ON y.ID = x.ID;  
SELECT * FROM @x;  
GO

Вот результирующий набор.

ID     Value  
------ -----  
1      100  
2      200  
(2 row(s) affected)

UPDATE утверждение с ссылками на CTE, которые неправильно совпадают.

USE tempdb;  
GO  
DECLARE @x TABLE (ID INT, Value INT);  
DECLARE @y TABLE (ID INT, Value INT);  
INSERT @x VALUES (1, 10), (2, 20);  
INSERT @y VALUES (1, 100),(2, 200);  
  
WITH cte AS (SELECT * FROM @x)  
UPDATE cte   -- cte isn't referenced by the alias.  
SET Value = y.Value  
FROM cte AS x  -- cte is assigned an alias.  
INNER JOIN @y AS y ON y.ID = x.ID;   
SELECT * FROM @x;   
GO

Вот результирующий набор.

ID     Value  
------ -----  
1      100  
2      100  
(2 row(s) affected)

Поведение блокировки

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

Если включена оптимизированная блокировка, некоторые аспекты поведения блокировки для UPDATE изменения. Например, монопольные блокировки (X) не хранятся до завершения транзакции. Дополнительные сведения см. в оптимизированныхблокировки.

Поведение при логировании

Оператор UPDATE регистрируется; однако частичные обновления для типов данных с большим значением с использованием . Клаузы WRITE минимально логированы. Дополнительные сведения см. ниже в подразделе "Обновление типов данных большого объема" приведенного ранее раздела "Типы данных".

Security

Permissions

Разрешения UPDATE необходимы для целевой таблицы. SELECT Разрешения также требуются для обновления таблицы, если UPDATE в операторе есть клауза WHERE или если выражение в предложении SET использует столбец в таблице.

UPDATE По умолчанию права доступны для членов sysadmin роли фиксированного сервера, ролей db_owner базы данных и db_datawriter владельца таблицы. Члены ролей sysadmin, db_owner и db_securityadmin, а также владелец таблицы могут передавать разрешения другим пользователям.

Examples

Category	Используемые элементы синтаксиса
Базовый синтаксис	UPDATE
Ограничение обновляемых строк	ГДЕ * ВЕРХ * С общим выражением таблицы * ГДЕ ТОК
Установка значений столбца	вычисляемые значения * составные операторы * значения по умолчанию * вложенные запросы
Указание целевых объектов, отличных от стандартных таблиц	представления * табличные переменные * псевдонимы таблицы
Обновление данных на основе данных из других таблиц	FROM
Обновление строк в удаленной таблице	связанный сервер * OPENQUERY * OPENDATASOURCE
Обновление типов данных больших объектов	. ЗАПИСЬ * OPENROWSET
Обновление определяемых пользователем типов данных	Пользовательские типы
Переопределение поведения по умолчанию для оптимизатора запросов с помощью указаний	табличные подсказки * подсказки запросов
Фиксация результатов UPDATE утверждения	Предложение OUTPUT
Использование UPDATE в других высказываниях	Хранимые процедуры * TRY... ЛОВИТЬ

Основной синтаксис

Примеры в этом разделе демонстрируют базовую функциональность UPDATE оператора с использованием минимально необходимого синтаксиса.

A. Использование простого UPDATE утверждения

В следующем примере обновляется один столбец для всех строк в таблице Person.Address.

USE AdventureWorks2022;  
GO  
UPDATE Person.Address  
SET ModifiedDate = GETDATE();

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

В следующем примере выполняется обновление значений в столбцах Bonus, CommissionPct и SalesQuota для всех строк в таблице SalesPerson.

USE AdventureWorks2022;  
GO  
UPDATE Sales.SalesPerson  
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;  
GO

Ограничение обновляемых строк

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

C. Применение предложения WHERE

В следующем примере предложение WHERE используется для указания строк, которые необходимо обновить. Инструкция обновляет значение в столбце Color таблицы Production.Product для всех строк, в которых имеется существующее значение Red в столбце Color и имеется значение в столбце Name, который начинается с Road-250.

USE AdventureWorks2022;  
GO  
UPDATE Production.Product  
SET Color = N'Metallic Red'  
WHERE Name LIKE N'Road-250%' AND Color = N'Red';  
GO

D. Использование предложения TOP

В следующих примерах используется клауза TOP для ограничения количества строк, изменяемых в операторе UPDATE . Когда TOP (n) используется с UPDATE, операция обновления выполняется при случайном выборе 'n' количества строк. В следующем примере в столбце VacationHours для случайных 10 строк таблицы Employee значение меняется на 25 %.

USE AdventureWorks2022;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO

Если нужно применить изменения с предложением TOP в определенной последовательности, укажите во вложенной инструкции выборки инструкцию ORDER BY. В следующем примере изменяется длительность отпуска для 10 сотрудников, имеющих наибольший стаж работы.

UPDATE HumanResources.Employee  
SET VacationHours = VacationHours + 8  
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee  
     ORDER BY HireDate ASC) AS th  
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;  
GO

E. Использование предложения WITH обобщенное_табличное_выражение

В следующем примере обновляется значение PerAssemblyQty для всех частей и компонентов, прямо или косвенно используемых для создания ProductAssemblyID 800. Обобщенное табличное выражение возвращает иерархический список частей, которые непосредственно используются для сборки ProductAssemblyID 800, и частей, которые используются для сборки этих компонентов, и т. д. Изменяются только строки, возвращенные обобщенным табличным выражением.

USE AdventureWorks2022;  
GO  
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS  
(  
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,  
        b.EndDate, 0 AS ComponentLevel  
    FROM Production.BillOfMaterials AS b  
    WHERE b.ProductAssemblyID = 800  
          AND b.EndDate IS NULL  
    UNION ALL  
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,  
        bom.EndDate, ComponentLevel + 1  
    FROM Production.BillOfMaterials AS bom   
        INNER JOIN Parts AS p  
        ON bom.ProductAssemblyID = p.ComponentID  
        AND bom.EndDate IS NULL  
)  
UPDATE Production.BillOfMaterials  
SET PerAssemblyQty = c.PerAssemblyQty * 2  
FROM Production.BillOfMaterials AS c  
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID  
WHERE d.ComponentLevel = 0;

F. Использование предложения WHERE CURRENT OF

В следующем примере предложение WHERE CURRENT OF используется только для обновления строк, на которых установлен курсор. Когда курсор основан на соединении, изменяется только указанное table_name в UPDATE операторе. Другие таблицы, участвующие в курсоре, не затрагиваются.

USE AdventureWorks2022;  
GO  
DECLARE complex_cursor CURSOR FOR  
    SELECT a.BusinessEntityID  
    FROM HumanResources.EmployeePayHistory AS a  
    WHERE RateChangeDate <>   
         (SELECT MAX(RateChangeDate)  
          FROM HumanResources.EmployeePayHistory AS b  
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;  
OPEN complex_cursor;  
FETCH FROM complex_cursor;  
UPDATE HumanResources.EmployeePayHistory  
SET PayFrequency = 2   
WHERE CURRENT OF complex_cursor;  
CLOSE complex_cursor;  
DEALLOCATE complex_cursor;  
GO

Установка значений столбца

Примеры в этом разделе демонстрируют обновление столбцов с помощью вычисленных значений, подзапросов и DEFAULT значений.

G. Указание вычисляемого значения

В следующих примерах используются вычисленные значения в операторе UPDATE . В примере удваивается значение столбца ListPrice для всех строк в таблице Product.

USE AdventureWorks2022;  
GO  
UPDATE Production.Product  
SET ListPrice = ListPrice * 2;  
GO

H. Задание составного оператора

В следующем примере демонстрируется использование переменной @NewPrice для увеличения цены красных велосипедов прибавлением 10 к текущей цене.

USE AdventureWorks2022;  
GO  
DECLARE @NewPrice INT = 10;  
UPDATE Production.Product  
SET ListPrice += @NewPrice  
WHERE Color = N'Red';  
GO

В следующем примере используется составной оператор += для добавления данных ' - tool malfunction' к существующему значению в столбце Name для строк, имеющих значение ScrapReasonID от 10 до 12.

USE AdventureWorks2022;  
GO  
UPDATE Production.ScrapReason   
SET Name += ' - tool malfunction'  
WHERE ScrapReasonID BETWEEN 10 and 12;

I. Указание подзапроса в SET предложении

В следующем примере используется подзапрос в SET клаузе для определения значения, которое используется для обновления столбца. Вложенный запрос должен возвращать только скалярное значение (то есть одно значение для каждой строки). В примере изменяется столбец SalesYTD в таблице SalesPerson для отображения самой последней информации о продажах, зафиксированной в таблице SalesOrderHeader. Вложенный запрос проводит статистическую обработку сведений о продажах по всем продавцам в инструкции UPDATE.

USE AdventureWorks2022;  
GO  
UPDATE Sales.SalesPerson  
SET SalesYTD = SalesYTD +   
    (SELECT SUM(so.SubTotal)   
     FROM Sales.SalesOrderHeader AS so  
     WHERE so.OrderDate = (SELECT MAX(OrderDate)  
                           FROM Sales.SalesOrderHeader AS so2  
                           WHERE so2.SalesPersonID = so.SalesPersonID)  
     AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID  
     GROUP BY so.SalesPersonID);  
GO

J. Обновление строк с помощью DEFAULT значений

В следующем примере для столбца CostRate задается значение по умолчанию (0,00) для всех строк, значение CostRate которых больше 20.00.

USE AdventureWorks2022;  
GO  
UPDATE Production.Location  
SET CostRate = DEFAULT  
WHERE CostRate > 20.00;

Указание целевых объектов, отличных от стандартных таблиц

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

K. Указание представления в качестве целевого объекта

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

USE AdventureWorks2022;  
GO  
UPDATE Person.vStateProvinceCountryRegion  
SET CountryRegionName = 'United States of America'  
WHERE CountryRegionName = 'United States';

L. Задание псевдонима таблицы в качестве целевого объекта

В следующем примере производится обновление строк в таблице Production.ScrapReason. Псевдоним таблицы, назначенный ScrapReason в клаузе FROM, указывается как целевой объект в клаузе UPDATE .

USE AdventureWorks2022;  
GO  
UPDATE sr  
SET sr.Name += ' - tool malfunction'  
FROM Production.ScrapReason AS sr  
JOIN Production.WorkOrder AS wo   
     ON sr.ScrapReasonID = wo.ScrapReasonID  
     AND wo.ScrappedQty > 300;

M. Задание табличной переменной в качестве целевого объекта

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

USE AdventureWorks2022;  
GO  
-- Create the table variable.  
DECLARE @MyTableVar TABLE (  
    EmpID INT NOT NULL,  
    NewVacationHours INT,  
    ModifiedDate DATETIME);  
  
-- Populate the table variable with employee ID values from HumanResources.Employee.  
INSERT INTO @MyTableVar (EmpID)  
    SELECT BusinessEntityID FROM HumanResources.Employee;  
  
-- Update columns in the table variable.  
UPDATE @MyTableVar  
SET NewVacationHours = e.VacationHours + 20,  
    ModifiedDate = GETDATE()  
FROM HumanResources.Employee AS e   
WHERE e.BusinessEntityID = EmpID;  
  
-- Display the results of the UPDATE statement.  
SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar  
ORDER BY EmpID;  
GO

Обновление данных на основе данных из других таблиц

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

N. Использование UPDATE оператора с информацией из другой таблицы

В следующем примере изменяется столбец SalesYTD в таблице SalesPerson для отображения самой последней информации о продажах, зафиксированной в таблице SalesOrderHeader.

USE AdventureWorks2022;  
GO  
UPDATE Sales.SalesPerson  
SET SalesYTD = SalesYTD + SubTotal  
FROM Sales.SalesPerson AS sp  
JOIN Sales.SalesOrderHeader AS so  
    ON sp.BusinessEntityID = so.SalesPersonID  
    AND so.OrderDate = (SELECT MAX(OrderDate)  
                        FROM Sales.SalesOrderHeader  
                        WHERE SalesPersonID = sp.BusinessEntityID);  
GO

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

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

USE AdventureWorks2022;  
GO  
UPDATE Sales.SalesPerson  
SET SalesYTD = SalesYTD +   
    (SELECT SUM(so.SubTotal)   
     FROM Sales.SalesOrderHeader AS so  
     WHERE so.OrderDate = (SELECT MAX(OrderDate)  
                           FROM Sales.SalesOrderHeader AS so2  
                           WHERE so2.SalesPersonID = so.SalesPersonID)  
     AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID  
     GROUP BY so.SalesPersonID);  
GO

Обновление строк в удаленной таблице

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

O. Обновление данных в удаленной таблице с использованием связанного сервера

В следующем примере обновляется таблица на удаленном сервере. Этот пример начинается с создания ссылки на удаленный источник данных с помощью хранимой процедуры sp_addlinkedserver. Затем имя связанного сервера MyLinkedServer указывается в качестве одного из четырех компонентов имени объекта в формате сервер.каталог.схема.объект. Обратите внимание, что необходимо указать действительное имя сервера для @datasrc.

USE master;  
GO  
-- Create a link to the remote data source.   
-- Specify a valid server name for @datasrc as 'server_name' or 'server_nameinstance_name'.  
  
EXEC sp_addlinkedserver @server = N'MyLinkedServer',  
    @srvproduct = N' ',  
    @provider = N'SQLNCLI10',   
    @datasrc = N'<server name>',  
    @catalog = N'AdventureWorks2022';  
GO  
USE AdventureWorks2022;  
GO  
-- Specify the remote data source using a four-part name   
-- in the form linked_server.catalog.schema.object.  
  
UPDATE MyLinkedServer.AdventureWorks2022.HumanResources.Department  
SET GroupName = N'Public Relations'  
WHERE DepartmentID = 4;

P. Обновление данных в удаленной таблице с помощью функции OPENQUERY

В следующем примере выполняется обновление строки в удаленной таблице с помощью вызова функции OPENQUERY, возвращающей набор строк. В этом примере используется имя связанного сервера, созданного в предыдущем примере.

UPDATE OPENQUERY (MyLinkedServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4')   
SET GroupName = 'Sales and Marketing';

Q. Обновление данных в удаленной таблице с помощью функции OPENDATASOURCE

В следующем примере выполняется обновление строки в удаленной таблице с помощью вызова функции OPENDATASOURCE, возвращающей набор строк. Определите допустимое имя сервера для источника данных, используя формат server_name или server_name\instance_name. Возможно, потребуется настроить экземпляр SQL Server для нерегламентированных распределенных запросов. Дополнительные сведения см. в статье Параметр конфигурации сервера "ad hoc distributed queries".

UPDATE OPENDATASOURCE('SQLNCLI', 'Data Source=<server name>;Integrated Security=SSPI').AdventureWorks2022.HumanResources.Department
SET GroupName = 'Sales and Marketing' WHERE DepartmentID = 4;

Обновление типов данных больших объектов

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

R. Использование UPDATE с . WRITE для изменения данных в столбце nvarchar(max)

В следующем примере предложение .WRITE используется для обновления частичного значения столбца DocumentSummary типа nvarchar(max) в таблице Production.Document. Слово components заменяется словом features путем указания слова для замены, начального положения (смещения) заменяемого слова в существующих данных и количества заменяемых символов (длины). В этом примере также используется предложение OUTPUT для возврата исходного и преобразованного образов столбца DocumentSummary в табличную переменную @MyTableVar.

USE AdventureWorks2022;  
GO  
DECLARE @MyTableVar TABLE (  
    SummaryBefore NVARCHAR(max),  
    SummaryAfter NVARCHAR(max));  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N'features',28,10)  
OUTPUT deleted.DocumentSummary,   
       inserted.DocumentSummary   
    INTO @MyTableVar  
WHERE Title = N'Front Reflector Bracket Installation';  
SELECT SummaryBefore, SummaryAfter   
FROM @MyTableVar;  
GO

S. Использование UPDATE с . WRITE для добавления и удаления данных в столбце nvarchar(max)

В следующем примере данные добавляются в столбец типа nvarchar(max), имеющий текущее значение NULL, и удаляются из него. Потому что . Предложение WRITE не может использоваться для изменения столбца NULL, столбец сначала заполняется временными данными. Затем они заменяются правильными данными с помощью предложения .WRITE. В дополнительных примерах данные добавляются в конец значения столбца, удаляются из столбца (усекаются) и, наконец, удаляются частичные данные из столбца. Операторы SELECT отображают модификацию данных, генерируемую каждым UPDATE оператором.

USE AdventureWorks2022;  
GO  
-- Replacing NULL value with temporary data.  
UPDATE Production.Document  
SET DocumentSummary = N'Replacing NULL value'  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
SELECT DocumentSummary   
FROM Production.Document  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
-- Replacing temporary data with the correct data. Setting @Length to NULL   
-- truncates all existing data from the @Offset position.  
UPDATE Production.Document  
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
SELECT DocumentSummary   
FROM Production.Document  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
-- Appending additional data to the end of the column by setting   
-- @Offset to NULL.  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
SELECT DocumentSummary   
FROM Production.Document  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
-- Removing all data from @Offset to the end of the existing value by   
-- setting expression to NULL.   
UPDATE Production.Document  
SET DocumentSummary .WRITE (NULL, 56, 0)  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
SELECT DocumentSummary   
FROM Production.Document  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
-- Removing partial data beginning at position 9 and ending at   
-- position 21.  
UPDATE Production.Document  
SET DocumentSummary .WRITE ('',9, 12)  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO  
SELECT DocumentSummary   
FROM Production.Document  
WHERE Title = N'Crank Arm and Tire Maintenance';  
GO

T. Использование UPDATE с помощью OPENROWSET для модификации varbinary(max) столбца

В следующем примере существующий образ в столбце типа varbinary(max) заменяется новым образом. Для загрузки образа в столбец используется функция OPENROWSET с параметром BULK. В этом примере предполагается, что по заданному пути существует файл с именем Tires.jpg.

USE AdventureWorks2022;  
GO  
UPDATE Production.ProductPhoto  
SET ThumbNailPhoto = (  
    SELECT *  
    FROM OPENROWSET(BULK 'c:Tires.jpg', SINGLE_BLOB) AS x )  
WHERE ProductPhotoID = 1;  
GO

U. Использование UPDATE для изменения данных FILESTREAM

В следующем примере оператор UPDATE используется для изменения данных в файловом файле. Этот метод не рекомендуется для потоковой передачи больших объемов данных в файл. Используйте соответствующие интерфейсы Win32. В следующем примере любой текст в записи файла заменяется текстом Xray 1. Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

UPDATE Archive.dbo.Records  
SET [Chart] = CAST('Xray 1' as VARBINARY(max))  
WHERE [SerialNumber] = 2;

Обновление определяемых пользователем типов данных

В следующих примерах изменяются значения в столбцах определяемых пользователем типов данных CLR. Описываются три метода. Дополнительные сведения об определяемых пользователем столбцах см. в разделе Определяемые пользователем типы данных CLR.

V. Использование системных типов данных

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

UPDATE dbo.Cities  
SET Location = CONVERT(Point, '12.3:46.2')  
WHERE Name = 'Anchorage';

W. Вызов метода

Определяемый пользователем тип можно обновить путем вызова метода, отмеченного в качестве мутатора определяемого пользователем типа, для выполнения обновления. Следующий пример вызывает метод мутатора типа Point с именем SetXY. Это обновляет состояние экземпляра типа.

UPDATE dbo.Cities  
SET Location.SetXY(23.5, 23.5)  
WHERE Name = 'Anchorage';

X. Изменение значения свойства или элемента данных

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

UPDATE dbo.Cities  
SET Location.X = 23.5  
WHERE Name = 'Anchorage';

Переопределение поведения по умолчанию для оптимизатора запросов с помощью указаний

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

Caution

Так как оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения для запроса, мы рекомендуем использовать указания только в качестве последнего средства опытными разработчиками и администраторами баз данных.

Y. Задание табличной подсказки

В следующем примере задается табличное указание TABLOCK. Эта подсказка указывает, что общий блокировка принимается на столе Production.Product и сохраняется до конца UPDATE оператора.

USE AdventureWorks2022;  
GO  
UPDATE Production.Product  
WITH (TABLOCK)  
SET ListPrice = ListPrice * 1.10  
WHERE ProductNumber LIKE 'BK-%';  
GO

Z. Задание подсказки в запросе

Следующий пример задаёт подсказкуOPTIMIZE FOR (@variable) запроса в операторе UPDATE . Эта подсказка указывает на необходимость использования оптимизатором запросов при компиляции и оптимизации запросов конкретного значения локальной переменной. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения.

USE AdventureWorks2022;  
GO  
CREATE PROCEDURE Production.uspProductUpdate  
@Product NVARCHAR(25)  
AS  
SET NOCOUNT ON;  
UPDATE Production.Product  
SET ListPrice = ListPrice * 1.10  
WHERE ProductNumber LIKE @Product  
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );  
GO  
-- Execute the stored procedure   
EXEC Production.uspProductUpdate 'BK-%';

Захват результатов UPDATE утверждения

Примеры в этом разделе демонстрируют, как использовать клаузу OUTPUT для возврата информации или выражений, основанных на каждой строке, на которую влияет UPDATE оператор. Эти результаты могут быть возвращены приложению, например для вывода подтверждающих сообщений, архивирования и т. п.

AA. Использование UPDATE с клаузой OUTPUT

В следующем примере значения в столбце VacationHours в Employee изменяются на 25 % для сотрудников, у которых менее 10 часов отпуска, а в столбец ModifiedDate заносится текущая дата. Предложение OUTPUT возвращает значение VacationHours, существующее до применения инструкции UPDATE в столбце deleted.VacationHours, и обновленное значение в столбце inserted.VacationHours к табличной переменной @MyTableVar.

Две следующие инструкции SELECT возвращают значения в табличную переменную @MyTableVar, а результаты операции обновления — в таблицу Employee. Дополнительные примеры использования предложения OUTPUT см. в статье Предложение OUTPUT (Transact-SQL).

USE AdventureWorks2022;  
GO  

--Display the initial data of the table to be updated.  
SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate  
FROM HumanResources.Employee
WHERE VacationHours < 10  
GO  

DECLARE @MyTableVar TABLE (  
    EmpID int NOT NULL,  
    OldVacationHours smallint,  
    NewVacationHours smallint,  
    ModifiedDate datetime);  
UPDATE HumanResources.Employee  
SET VacationHours =  VacationHours * 1.25,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
      deleted.VacationHours,  
      inserted.VacationHours,  
      inserted.ModifiedDate  
INTO @MyTableVar
    WHERE VacationHours < 10  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours
, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  

GO  
--Display the result set of the table.  
SELECT BusinessEntityID, VacationHours, ModifiedDate, HireDate  
FROM HumanResources.Employee
    WHERE VacationHours < 10  
GO

Использование UPDATE в других высказываниях

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

AB. Использование UPDATE в хранящейся процедуре

В следующем примере используется UPDATE оператор в хранимой процедуре. Процедура принимает один входной параметр, @NewHoursи один выходной параметр @RowCount. @NewHours Значение параметра используется в UPDATE операторе для обновления столбца VacationHours в таблице HumanResources.Employee. Выходной параметр @RowCount используется для возврата значения числа задействованных строк в локальную переменную. Выражение CASE используется в SET предложении для условного определения значения, задаваемого для VacationHours. Если для сотрудника применяется почасовая ставка оплаты (SalariedFlag = 0), то в столбце VacationHours устанавливается текущее количество часов плюс значение, заданное в @NewHours. В противном случае в столбце VacationHours указывается значение, заданное в @NewHours.

USE AdventureWorks2022;  
GO  
CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours SMALLINT  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  
  
EXEC HumanResources.Update_VacationHours 40;

AC. Использование UPDATE в TRY... CATCH Block

Следующий пример использует UPDATE оператор в TRY... Блок CATCH для обработки ошибок выполнения, которые могут возникнуть во время обновления.

USE AdventureWorks2022;  
GO  
BEGIN TRANSACTION;  
  
BEGIN TRY  
    -- Intentionally generate a constraint violation error.  
    UPDATE HumanResources.Department  
    SET Name = N'MyNewName'  
    WHERE DepartmentID BETWEEN 1 AND 2;  
END TRY  
BEGIN CATCH  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

AD. Использование простого UPDATE утверждения

В следующих примерах показано, как можно повлиять на все строки, если предложение WHERE не используется для указания строки (или строк) для обновления.

Происходит обновление значений в столбцах EndDate и CurrentFlag для всех строк в таблице DimEmployee.

-- Uses AdventureWorks  
  
UPDATE DimEmployee  
SET EndDate = '2010-12-31', CurrentFlag='False';

Также можно использовать вычисленные значения в UPDATE операторе. В следующем примере удваивается значение столбца ListPrice для всех строк в таблице Product.

-- Uses AdventureWorks  
  
UPDATE DimEmployee  
SET BaseRate = BaseRate * 2;

AE. Использование UPDATE оператора с клаузой WHERE

В следующем примере предложение WHERE используется для указания строк, которые необходимо обновить.

-- Uses AdventureWorks  
  
UPDATE DimEmployee  
SET FirstName = 'Gail'  
WHERE EmployeeKey = 500;

AF. Использование оператора UPDATE с меткой

Следующий пример показывает использование LABEL для этого UPDATE оператора.

-- Uses AdventureWorks  
  
UPDATE DimProduct  
SET ProductSubcategoryKey = 2   
WHERE ProductKey = 313  
OPTION (LABEL = N'label1');

AG. Использование UPDATE оператора с информацией из другой таблицы

В этом примере создается таблица для хранения итогов продаж по годам. Обновляются итоги продаж за 2004 год с помощью инструкции SELECT в таблице FactInternetSales.

-- Uses AdventureWorks  
  
CREATE TABLE YearlyTotalSales (  
    YearlySalesAmount MONEY NOT NULL,  
    Year SMALLINT NOT NULL )  
WITH ( DISTRIBUTION = REPLICATE );  
  
INSERT INTO YearlyTotalSales VALUES (0, 2004);  
INSERT INTO YearlyTotalSales VALUES (0, 2005);  
INSERT INTO YearlyTotalSales VALUES (0, 2006);  
  
UPDATE YearlyTotalSales  
SET YearlySalesAmount=  
(SELECT SUM(SalesAmount) FROM FactInternetSales WHERE OrderDateKey >=20040000 AND OrderDateKey < 20050000)  
WHERE Year=2004;  
  
SELECT * FROM YearlyTotalSales;

AH. Соединение ANSI для инструкций обновления

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

CREATE TABLE dbo.Table1   
    (ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
CREATE TABLE dbo.Table2   
    (ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
INSERT INTO dbo.Table1 VALUES(1, 10.0);  
INSERT INTO dbo.Table2 VALUES(1, 0.0);  
GO  
UPDATE dbo.Table2   
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB  
FROM dbo.Table2   
    INNER JOIN dbo.Table1   
    ON (dbo.Table2.ColA = dbo.Table1.ColA);  
GO  
SELECT ColA, ColB   
FROM dbo.Table2;
GO

См. также

CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
Курсоры (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
Функции текста и изображения (Transact-SQL)
С common_table_expression (Transact-SQL)
FILESTREAM (SQL Server)
Поддержка параметров сортировки и Юникода
Однобайтовые и многобайтовые кодировки

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-06-24