UPDATE (Transact-SQL)

Синтаксис

[ 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}

Аргументы

• WITH <common_table_expression>
  Задает временный именованный результирующий набор или представление, которые называются обобщенным табличным выражением (CTE), определяемым в пределах области действия инструкции UPDATE. Результирующий набор обобщенного табличного выражения, на который ссылается инструкция UPDATE, является производным простого запроса. Дополнительные сведения см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).

• TOP ( expression**)** [ PERCENT ]
  Указывает количество или процент строк, которые будут обновлены. expression может быть либо числом, либо процентной долей строк.

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

  В инструкциях INSERT, UPDATE, MERGE и DELETE выражение expression должно быть заключено в скобки. Дополнительные сведения см. в разделе TOP (Transact-SQL).

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

• server_name
  Имя связанного сервера, на котором расположена таблица или индексированное представление. Аргумент server_name можно задавать в виде имени связанного сервера или с помощью функции OPENDATASOURCE.

  Если аргумент server_name задается в виде связанного сервера, то необходимы аргументы database_name и schema_name. Если аргумент server_name задается с помощью функции OPENDATASOURCE, то аргументы database_name и schema_name могут применяться не ко всем источникам данных, в зависимости от возможностей поставщика OLE DB, который обращается к удаленному объекту. Дополнительные сведения см. в разделах Распределенные запросы.

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

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

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

• rowset_function_limited
  Либо функция OPENQUERY, либо функция OPENROWSET. Использование этих функций зависит от возможностей поставщика OLE DB, который обращается к удаленному объекту. Дополнительные сведения см. в разделе Распределенные запросы.

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

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

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

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

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

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

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

• { += | -= | *= | /= | %= | &= | ^= | |= }
  Составной оператор присваивания:

  += сложить и присвоить

  -= вычесть и присвоить

  *= умножить и присвоить

  /= разделить и присвоить

  %= получить остаток от деления и присвоить

  &= выполнить побитовое И и присвоить

  = выполнить побитовое исключающее ИЛИ и присвоить

  = выполнить побитовое ИЛИ и присвоить

  Дополнительные сведения см. в разделе Составные операторы (Transact-SQL).

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

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

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

• **.**WRITE (expression,@Offset,@Length)
  Указывает, что должен быть изменен раздел значения column_name . expression заменяет в column_name@Length элементы, начиная с позиции @Offset. С этим предложением можно указывать только столбцы типа 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. Аргумент @Offset — отсчитываемая от нуля порядковая позиция с типом данных bigint, может быть отрицательным числом. Если аргумент @Offset имеет значение NULL, операция обновления добавляет значение expression в конец существующего значения аргумента column_name, а аргумент @Length пропускается. Если значение аргумента @Offset больше, чем длина значения аргумента column_name, компонент Database Engine возвращает ошибку. Если сумма значений @Offset и @Length превышает длину базового значения столбца, удаление выполняется до последнего символа этого значения. Если сумма значений @Offset и LEN(expression) больше, чем базовый объявленный размер, возникает ошибка.

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

  Дополнительные сведения см. в разделе «Примечания».

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

  SET @variable = column = expression присваивает переменной то же значение, что и столбцу. Это отличается от предложения SET @variable = column, column = expression, присваивающего переменной значение столбца до обновления.

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

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

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

  Представление с триггером INSTEAD OF UPDATE не может быть целью инструкции UPDATE с предложением FROM.

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

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

  • В поисковых обновлениях задается условие поиска строк, предназначенных к удалению.

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

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

• CURRENT OF
  Определяет, что обновление выполняется в текущей позиции указанного курсора.

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

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

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

• OPTION ( <подсказка_запроса> [ ,... n ] )
  Определяет, что для настройки способа, которым компонент Database Engine обрабатывает инструкцию, используются подсказки оптимизатора. Дополнительные сведения см. в разделе Подсказки в запросах (Transact-SQL).

Рекомендации

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

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

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT 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 AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT 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

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

Инструкция SET ROWCOUNT не будет оказывать влияния на инструкции DELETE, INSERT и UPDATE в следующем выпуске SQL Server. В новых разработках следует избегать совместного использования инструкции SET ROWCOUNT с инструкциями DELETE, INSERT и UPDATE. Также необходимо запланировать внесение изменений в используемые приложения. Рекомендуется использовать синтаксис TOP.

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

Типы данных

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

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

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

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

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

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

Чтобы заменить или изменить большие блоки данных типов text, ntext или image, вместо UPDATE используется инструкция WRITETEXT или UPDATETEXT.

Важно!

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

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

Для инструкции UPDATE можно реализовать обработку ошибок, заключив ее в конструкцию TRY…CATCH. Дополнительные сведения см. в разделе Использование конструкции TRY...CATCH в языке Transact-SQL.

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

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

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

Совместимость

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

Если для операций UPDATE по отношению к таблице определен триггер INSTEAD OF, вместо инструкции UPDATE запускается этот триггер. Предыдущие версии SQL Server поддерживали для инструкции UPDATE и других инструкций изменения данных только триггеры AFTER.

Ограничения

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

Значение параметра SET ROWCOUNT не учитывается инструкциями UPDATE для удаленных таблиц и для локальных и удаленных секционированных представлений.

Если обобщенное табличное выражение указывается в качестве цели инструкции UPDATE, должны совпадать все ссылки на это выражение в инструкции. Например, если для обобщенного табличного выражения в предложении FROM назначается псевдоним, то этот псевдоним должен использоваться для всех остальных ссылок на обобщенное табличное выражение. Однозначные ссылки на обобщенное табличное выражение необходимы в силу того, что обобщенное табличное выражение не имеет идентификатора объекта, который используется 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 statement with CTE references that are incorrectly matched.
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 is not 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 протоколируется полностью, однако обновления с помощью метода .WRITE, вставляющие или присоединяющие новые данные, протоколируются на минимальном уровне, если установлена простая модель восстановления базы данных или модель восстановления с неполным протоколированием. Протоколирование не сокращается до минимума, если при помощи метода .WRITE обновляются существующие значения. Дополнительные сведения см. в разделе Операции, для которых возможно минимальное протоколирование.

Безопасность

Разрешения

Примеры

Базовый синтаксис

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

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

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

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

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

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

USE AdventureWorks2008R2;
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; 

USE AdventureWorks2008R2;
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

Настройка значений столбца

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

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

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

USE AdventureWorks2008R2;
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

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

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

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

USE AdventureWorks2008R2;
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;

USE AdventureWorks2008R2;
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

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

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

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

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI10', 
    @datasrc = N'<server name>',
    @catalog = N'AdventureWorks2008R2';
GO
USE AdventureWorks2008R2;
GO
-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

UPDATE MyLinkServer.AdventureWorks.HumanResources.Department
SET GroupName = N'Public Relations'
WHERE DepartmentID = 4;

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

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

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

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

USE AdventureWorks2008R2;
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

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

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

USE AdventureWorks2008R2;
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;

USE AdventureWorks2008R2;
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

См. также

Справочник

Основные понятия