UPDATE (Transact-SQL)

Sintaxe

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

Argumentos

• WITH <common_table_expression>
  Especifica a exibição ou o conjunto de resultados nomeado temporário, também conhecido como CTE (expressão de tabela comum), definido dentro do escopo da instrução UPDATE. O conjunto de resultados da CTE é derivado de uma consulta simples e é referenciado pela instrução UPDATE. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

• TOP ( expression**)** [ PERCENT ]
  Especifica o número ou a porcentagem de linhas que serão atualizadas. expression pode ser um número ou uma porcentagem de linhas.

  As linhas referidas na expressão TOP usadas com INSERT, UPDATE, MERGE ou DELETE não são organizadas em nenhuma ordem.

  São necessários parênteses delimitando expression em TOP nas instruções INSERT, UPDATE, MERGE e DELETE. Para obter mais informações, consulte TOP (Transact-SQL).

• table_alias
  O alias especificado na cláusula FROM que representa a tabela ou exibição na qual as linhas devem ser atualizadas.

• server_name
  É o nome do servidor vinculado no qual a tabela ou exibição está localizada. server_name pode ser especificado como um nome de servidor vinculado ou usando a função OPENDATASOURCE.

  Quando server_name é especificado como um servidor vinculado, database_name e schema_name são necessários. Quando server_name é especificado com OPENDATASOURCE, database_name e schema_name podem não se aplicar a todas as fontes de dados e podem estar sujeitos aos recursos do provedor OLE DB que acessa o objeto remoto. Para obter mais informações, consulte Consultas distribuídas.

• database_name
  É o nome do banco de dados.

• schema_name
  É o nome do esquema ao qual a tabela ou exibição pertence.

• table_or view_name
  É o nome da tabela ou exibição na qual as linhas serão atualizadas. A exibição referenciada por table_or_view_name deve ser atualizável e fazer referência exata a uma tabela base na cláusula FROM da exibição. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).

• rowset_function_limited
  É a função OPENQUERY ou OPENROWSET. O uso destas funções está sujeito aos recursos do provedor OLE DB que acessa o objeto remoto. Para obter mais informações, consulte Consultas distribuídas.

• WITH ( <Table_Hint_Limited> )
  Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino. A palavra-chave WITH e os parênteses são necessários. NOLOCK e READUNCOMMITTED não são permitidos. Para obter informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).

• @table_variable
  Especifica uma variável table como uma origem de tabela.

• SET
  Especifica a lista de colunas ou nomes de variáveis a serem atualizados.

• column_name
  É uma coluna que contém os dados a serem alterados. column_name deve existir em table_or view_name. Colunas de identidade não podem ser atualizadas.

• expression
  É uma variável, valor literal, expressão ou uma instrução de subseleção (incluída com parênteses) que retorna um único valor. O valor retornado pela expression substitui o valor existente em column_name ou @variable.

  Observação
  Ao referenciar tipos de dados de caractere Unicode nchar, nvarchar e ntext, 'expression' deve ter a letra maiúscula 'N' como prefixo. Se 'N' não for especificada, o SQL Server converterá a cadeia de caracteres para a página de código que corresponde ao agrupamento padrão do banco de dados ou coluna. Qualquer caractere não localizado nessa página de código será perdido.

• DEFAULT
  Especifica que o valor padrão definido para a coluna deve substituir o valor existente na coluna. Isso também poderá ser usado para alterar a coluna para NULL se ela não tiver nenhum padrão e estiver definida para permitir valores nulos.

• { += | -= | *= | /= | %= | &= | ^= | |= }
  Operador de atribuição composto:

  += Somar e atribuir

  -= Subtrair e atribuir

  *= Multiplicar e atribuir

  /= Dividir e atribuir

  %= Módulo e atribuir

  &= AND de bit a bit e atribuir

  ^= XOR de bit a bit e atribuir

  |= OR de bit a bit e atribuir

  Para obter mais informações, consulte Operadores compostos (Transact-SQL).

• udt_column_name
  É uma coluna de tipo definido pelo usuário.

• property_name | field_name
  É uma propriedade pública ou membro de dados público de um tipo definido pelo usuário.

• method_name(argument [ ,... n] )
  É um método modificador público não estático de udt_column_name que usa um ou mais argumentos.

• **.**WRITE (expression,@Offset,@Length)
  Especifica que uma seção do valor de column_name deve ser modificada. expression substitui unidades de @Length iniciando em @Offset de column_name. Apenas colunas de varchar(max), nvarchar(max) ou varbinary(max) podem ser especificadas com esta cláusula. column_name não pode ser NULL e não pode ser qualificada com um nome ou alias de tabela.

  expression é o valor copiado para column_name. expression deve ser avaliada ou poder ser convertida implicitamente para o tipo column_name. Se expression estiver definida como NULL, @Length será ignorado e o valor em column_name será truncado no @Offset especificado.

  @Offset é o ponto de início no valor de column_name no qual a expression é gravada. @Offset é uma posição ordinal baseada em zero, é bigint e não pode ser um número negativo. Se @Offset for NULL, a operação de atualização acrescentará a expression no final do valor existente de column_name e @Length será ignorado. Se @Offset for maior do que o comprimento do valor de column_name, o Mecanismo de Banco de Dados retornará um erro. Se @Offset mais @Length exceder o final do valor subjacente na coluna, a exclusão ocorrerá até o último caractere do valor. Se @Offset mais LEN(expression) for maior do que o tamanho declarado subjacente, um erro será gerado.

  @Length é o comprimento da seção na coluna, iniciando em @Offset, que é substituído pela expression. @Length é bigint e não pode ser um número negativo. Se @Length for NULL, a operação de atualização removerá todos os dados de @Offset até o final do valor de column_name.

  Para obter mais informações, consulte Comentários.

• @variable
  É uma variável declarada definida como o valor retornado por expression.

  SET @variable = column = expression define a variável como o mesmo valor que a coluna. Isso difere de SET @variable = column, column = expression, que define a variável como o valor de pré-atualização da coluna.

• <OUTPUT_Clause>
  Retorna dados atualizados ou expressões com base neles, como parte da operação UPDATE. A cláusula OUTPUT não tem suporte em nenhuma instrução DML destinada a exibições ou tabelas remotas. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).

• FROM <table_source>
  Especifica que uma tabela, exibição ou origem de tabela derivada é usada para fornecer os critérios da operação de atualização. Para obter mais informações, consulte FROM (Transact-SQL).

  Se o objeto que está sendo atualizado for o mesmo que o objeto na cláusula FROM e houver apenas uma referência ao objeto na cláusula FROM, um alias do objeto poder ser especificado ou não. Se o objeto que está sendo atualizado aparecer mais de uma vez na cláusula FROM, uma, e apenas uma, referência ao objeto não deve especificar o alias da tabela. Todas as outras referências ao objeto na cláusula FROM devem incluir um alias de objeto.

  Uma exibição que tem um gatilho INSTEAD OF UPDATE não pode ser um destino de UPDATE com uma cláusula FROM.

  Observação
  Qualquer chamada para OPENDATASOURCE, OPENQUERY ou OPENROWSET na cláusula FROM é avaliada separada e independentemente de qualquer chamada para essas funções usadas como o destino da atualização, mesmo se argumentos idênticos forem fornecidos às duas chamadas. Em particular, as condições de filtro ou junção aplicadas no resultado de uma dessas chamadas não têm efeito sobre os resultado da outra.

• WHERE
  Especifica as condições que limitam as linhas que são atualizadas. Há duas formas de atualização com base na forma em que a clausula WHERE é usada:

  • Atualizações pesquisadas especificam um critério de pesquisa para qualificar as linhas a serem excluídas.

  • Atualizações posicionadas usam a cláusula CURRENT OF para especificar um cursor. A operação de atualização ocorre na posição atual do cursor.

• <search_condition>
  Especifica o critério a ser atendido para as linhas a serem atualizadas. O critério de pesquisa também pode ser o critério no qual uma junção é baseada. Não há nenhum limite para o número de predicados que podem ser incluídos em um critério de pesquisa. Para obter mais informações sobre critérios de pesquisa e predicados, consulte Critério de pesquisa (Transact-SQL).

• CURRENT OF
  Especifica que a atualização é executada na posição atual do cursor especificado.

• GLOBAL
  Especifica que cursor_name se refere a um cursor global.

• cursor_name
  É o nome do cursor aberto a partir do qual a busca deve ser feita. Se um cursor global e um cursor local com o nome cursor_name existirem, esse argumento fará referência ao cursor global se GLOBAL estiver especificado; caso contrário, fará referência ao cursor local. O cursor deve permitir atualizações.

• cursor_variable_name
  É o nome de uma variável de cursor. cursor_variable_name deve fazer referência a um cursor que permite atualizações.

• OPTION ( <query_hint> [ ,... n ] )
  Especifica que dicas de otimização são usadas para personalizar a maneira como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

Práticas recomendadas

Nomes de variáveis podem ser usados em instruções UPDATE para mostrar os valores novos e antigos afetados, mas isso deve ser usado apenas quando a instrução UPDATE afeta um único registro. Se a instrução UPDATE afetar vários registros, use a cláusula OUTPUT para retornar os valores novos e antigos de cada registro.

Tenha cuidado ao especificar a cláusula FROM para fornecer os critérios da operação de atualização. Os resultados de uma instrução UPDATE não serão definidos se a instrução incluir uma cláusula FROM que não esteja especificada de maneira que apenas um valor esteja disponível para cada ocorrência de coluna atualizada, isto é, se a instrução UPDATE não for determinística. Isso pode provocar resultados inesperados. Por exemplo, na instrução UPDATE do script a seguir, as duas linhas de Table1 atendem às qualificações da cláusula FROM no instrução UPDATE, mas não está definido qual linha de Table1 é usada para atualizar a linha de 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;

O mesmo problema pode ocorrer quando as cláusulas FROM e WHERE CURRENT OF forem combinadas. No exemplo a seguir, as duas linhas em Table2 atendem às qualificações da cláusula FROM na instrução UPDATE. Não está definido qual linha da Table2 será usada para atualizar a linha na 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

Suporte de compatibilidade

SET ROWCOUNT não afetará as instruções DELETE, INSERT e UPDATE em uma futura versão do SQL Server. Não use SET ROWCOUNT com instruções DELETE, INSERT e UPDATE em um novo trabalho de desenvolvimento e planeje modificar os aplicativos que a utilizam atualmente. Recomendamos o uso da sintaxe TOP.

O suporte ao uso de dicas de READUNCOMMITTED e NOLOCK na cláusula FROM que se aplicam à tabela de destino de uma instrução UPDATE ou DELETE será eliminado em uma versão futura do SQL Server. Evite usar essas dicas nesse contexto em desenvolvimentos novos e planeje modificar aplicativos que as usam atualmente.

Tipos de dados

Todas as colunas char e nchar são preenchidas à direita até o comprimento definido.

Se ANSI_PADDING estiver definido como OFF, todos os espaços à direita serão removidos dos dados inseridos em colunas varchar e nvarchar, exceto em cadeias de caracteres que contenham apenas espaços. Essas cadeias de caracteres são truncadas para uma cadeia de caracteres vazia. Se ANSI_PADDING estiver definido como ON, serão inseridos espaços à direita. O driver ODBC do Microsoft SQL Server e o OLE DB Provider for SQL Server definem automaticamente ANSI_PADDING ON para cada conexão. Isso pode ser configurado em fontes de dados ODBC ou por meio da configuração de atributos ou propriedades de conexão. Para obter mais informações, consulte SET ANSI_PADDING (Transact-SQL).

Atualizando tipos de dados de valor grande

Atualizando dados de FILESTREAM

Atualizando colunas text, ntext e image

A modificação de uma coluna text, ntext ou image com UPDATE inicializa a coluna, atribui um ponteiro de texto válido a ela e aloca pelo menos uma página de dados, a não ser que a coluna esteja sendo atualizada com NULL. Se a instrução UPDATE puder alterar mais de uma linha ao atualizar a chave de clustering e uma ou mais colunas text, ntext ou image, a atualização parcial dessas colunas será executada como uma substituição completa dos valores.

Para substituir ou modificar blocos grandes de dados text, ntext ou image, use WRITETEXT ou UPDATETEXT em vez da instrução UPDATE.

Importante
Os tipos de dados ntext, text e image serão removidos em uma versão futura do Microsoft SQL Server. Evite usar esses tipos de dados em novo projeto de desenvolvimento e planeje modificar os aplicativos que os utilizam atualmente. Em vez disso, use nvarchar(max), varchar(max) e varbinary(max). Para obter mais informações, consulte Usando tipos de dados de valor grande.

Tratamento de erros

Você pode implementar o tratamento de erros para a instrução INSERT especificando essa instrução em uma construção TRY.CATCH. Para obter mais informações, consulte Usando TRY...CATCH na Transact-SQL.

Se uma atualização em uma linha violar uma restrição ou regra, violar a configuração NULL da coluna ou se o novo valor for um tipo de dados incompatível, a instrução será cancelada, um erro será retornado e nenhum registro será atualizado.

Quando uma instrução UPDATE encontra um erro aritmético (estouro, divisão por zero ou um erro de domínio) durante a avaliação da expressão, a atualização não é executada. O restante do lote não é executado e uma mensagem de erro é retornada.

Se uma atualização em uma ou mais colunas que participam de um índice clusterizado fizer com que o tamanho do índice clusterizado e a linha excedam 8.060 bytes, a atualização falhará e uma mensagem de erro será retornada.

Interoperabilidade

Instruções UPDATE são permitidas no corpo de funções definidas pelo usuário apenas se a tabela que está sendo modificada for uma variável de tabela.

Quando um gatilho INSTEAD OF é definido em ações UPDADE em uma tabela, o gatilho é executado em vez da instrução UPDATE. Versões anteriores do SQL Server oferecem suporte apenas a gatilhos AFTER definidos na UPDATE e em outras instruções de modificação de dados.

Limitações e restrições

A cláusula FROM não pode ser especificada em uma instrução UPDATE que faça referência, direta ou indiretamente, a uma exibição que tenha um gatilho INSTEAD OF definido. Para obter mais informações sobre gatilhos INSTEAD OF, consulte CREATE TRIGGER (Transact-SQL).

A configuração da opção SET ROWCOUNT é ignorada para instruções UPDATE em tabelas remotas e exibições locais e remotas particionadas.

Quando uma CTE (expressão de tabela comum) for o destino de uma instrução UPDATE, todas as referências à CTE na instrução devem corresponder. Por exemplo, se for atribuído à CTE um alias na cláusula FROM, o alias deverá ser usado para todas as outras referências à CTE. São necessárias referências não ambíguas à CTE porque a CTE não tem ID de objeto, a qual o SQL Server usa para reconhecer a relação implícita entre um objeto e seu alias. em essa relação, o plano de consulta pode gerar comportamento de junção inesperado e resultados de consulta não intencionais. Os exemplos a seguir demonstram métodos corretos e incorretos de especificação de uma CTE quando a CTE for o objeto de destino da operação de atualização.

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

Aqui está o conjunto de resultados.

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

Aqui está o conjunto de resultados.

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

Comportamento de registro em log

A instrução UPDATE é completamente registrada em log. Porém atualizações .WRITE que inserem ou acrescentam novos dados serão minimamente registradas em log se o modelo de recuperação do banco de dados estiver definido como bulk-logged ou simples. O registro em log mínimo não é usado quando .WRITE é usado para atualizar valores existentes. Para obter mais informações, consulte Operações que podem ser minimamente registradas em log.

Segurança

Permissões

Exemplos

Sintaxe básica

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

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

Limitando as linhas que são atualizadas

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

Definindo valores de coluna

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;

Especificando objetos de destino diferentes de tabelas padrão

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

Atualizando dados com base em dados de outras tabelas

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

Atualizando linhas em uma tabela remota

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';

Atualizando tipos de dados de objeto grande

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;

Atualizando tipos definidos pelo usuário

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';

Substituindo o comportamento padrão do otimizador de consultas por meio de dicas

Os exemplos desta seção demonstram como usar dicas de tabela e de consulta para substituir temporariamente o comportamento padrão do otimizador de consultas durante o processamento da instrução UPDATE.

Cuidado
Como o otimizador de consultas do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que os desenvolvedores e administradores de banco de dados experientes usem as dicas apenas como um último recurso.

A. Especificando uma dica de tabela

O exemplo a seguir especifica a dica de tabela TABLOCK. Esta dica especifica que um bloqueio compartilhado é utilizado na tabela Production.Product e mantido até o término da instrução UPDATE.

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

B. Especificando uma dica de consulta

O exemplo a seguir especifica a dica de consultaOPTIMIZE FOR (@variable) na instrução UPDATE. A dica instrui o otimizador de consultas a usar um valor específico para uma variável local quando a consulta é compilada e otimizada. O valor é usado apenas durante a otimização e não durante a execução das consultas.

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-%';

Capturando os resultados da instrução 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

Usando UPDATE em outras instruções

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

Consulte também

Referência

Conceitos