ATUALIZAÇÃO (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Armazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

Altera dados existentes em uma tabela ou exibição no SQL Server. Para obter exemplos, consulte Exemplos.

Transact-SQL convenções de sintaxe

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

COM common_table_expression <>
Especifica o conjunto de resultados nomeado temporário ou exibição, também conhecido como expressão de tabela comum (CTE), definido dentro do escopo da instrução UPDATE. O conjunto de resultados CTE é derivado de uma consulta simples e é referenciado pela instrução UPDATE.

Expressões de tabela comuns também podem ser usadas com as instruções SELECT, INSERT, DELETE e CREATE VIEW. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

TOPO (expressão) [ PERCENTAGEM ]
Especifica o número ou a porcentagem de linhas que são atualizadas. A expressão pode ser um número ou uma porcentagem das linhas.

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

Parênteses que delimitam a expressão em TOP são necessários nas instruções INSERT, UPDATE e DELETE. Para obter mais informações, consulte TOP (Transact-SQL).

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

server_name
É o nome do servidor (usando um nome de servidor vinculado ou a função OPENDATASOURCE como o nome do servidor) no qual a tabela ou exibição está localizada. Se server_name for especificado, database_name e schema_name são necessários.

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 vista a partir da qual as linhas devem ser atualizadas. O modo de exibição referenciado por table_or_view_name deve ser atualizável e fazer referência exatamente a uma tabela base na cláusula FROM do modo de exibição. Para obter mais informações sobre modos de exibição atualizáveis, consulte CREATE VIEW (Transact-SQL).

rowset_function_limited
É a função OPENQUERY ou OPENROWSET , sujeita aos recursos do provedor.

COM (<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 obrigatórios. NOLOCK, READUNCOMMITTED, NOEXPAND e vários outros 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 de tabela como uma fonte de tabela.

SET
Especifica a lista de nomes de colunas ou 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. As colunas de identidade não podem ser atualizadas.

expression
É uma variável, valor literal, expressão ou uma instrução subselect (entre parênteses) que retorna um único valor. O valor retornado pela expressão substitui o valor existente em column_name ou @variable.

Note

Ao fazer referência aos tipos de dados de caracteres Unicode nchar, nvarchar e ntext, 'expressão' deve ser prefixada com a letra maiúscula 'N'. Se 'N' não for especificado, o SQL Server converte a cadeia de caracteres na página de código que corresponde ao agrupamento padrão do banco de dados ou coluna. Todos os caracteres não encontrados nesta página de código são perdidos.

DEFAULT
Especifica que o valor padrão definido para a coluna é substituir o valor existente na coluna. Isso também pode ser usado para alterar a coluna para NULL se a coluna não tiver padrão e for definida para permitir valores nulos.

+= | -= | *= | /= | %= | { &= | ^= | |= }
Operador de atribuição composta:
+= Adicionar e atribuir
-= Subtrair e atribuir
*= Multiplicar e atribuir
/= Dividir e atribuir
%= Modulo e atribuir
&= Bitwise E atribuir
^= XOR Bitwise e atribuir
|= Bitwise OU e atribuir

udt_column_name
É uma coluna de tipo definida pelo usuário.

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

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

. WRITE (expressão,@Deslocamento,@comprimento)
Especifica que uma seção do valor de column_name deve ser modificada. expression substitui as unidades @Length a partir de @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 qualificado com um nome de tabela ou alias de tabela.

expression é o valor que é copiado para column_name. expressão deve avaliar ou ser capaz de ser implicitamente moldada para o tipo column_name . Se expression for definido como NULL, @Length será ignorado e o valor em column_name será truncado no @Offset especificado.

@ Deslocamento é o ponto de partida no valor armazenado em column_name no qual a expressão é gravada. @ Deslocamento é uma posição de byte 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á expressão no final do valor de column_name existente e @Length será ignorado. Se @Offset for maior que o comprimento de byte do valor 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.

@ Comprimento é o comprimento da seção na coluna, começando por @Offset, que é substituído por expressão. @ O comprimento é grande 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 column_name .

Para obter mais informações, consulte Atualizando tipos de dados de grande valor.

@ variável
É uma variável declarada que é definida como o valor retornado pela expressão.

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

<OUTPUT_Clause>
Retorna dados atualizados ou expressões baseadas neles como parte da operação UPDATE. A cláusula OUTPUT não é suportada em nenhuma instrução DML direcionada a tabelas ou exibições remotas. Para obter mais informações sobre os argumentos e o comportamento dessa cláusula, consulte Cláusula OUTPUT (Transact-SQL).

DO table_source <>
Especifica que uma tabela, exibição ou fonte de tabela derivada é usada para fornecer os critérios para a 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 de objeto pode ou não ser especificado. Se o objeto que está sendo atualizado aparecer mais de uma vez na cláusula FROM, uma e apenas uma, a referência ao objeto não deverá especificar um alias de tabela. Todas as outras referências ao objeto na cláusula FROM devem incluir um alias de objeto.

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

Note

Qualquer chamada para OPENDATASOURCE, OPENQUERY ou OPENROWSET na cláusula FROM é avaliada separadamente e independentemente de qualquer chamada para essas funções usadas como destino da atualização, mesmo que argumentos idênticos sejam fornecidos para as duas chamadas. Em especial, as condições de filtragem ou de junção aplicadas ao resultado de uma dessas chamadas não têm qualquer efeito sobre os resultados da outra.

WHERE
Especifica as condições que limitam as linhas que são atualizadas. Existem duas formas de atualização com base na forma da cláusula WHERE é usada:

• As atualizações pesquisadas especificam uma condição de pesquisa para qualificar as linhas a serem excluídas.

• As 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 a condição a ser atendida para que as linhas sejam atualizadas. A condição de pesquisa também pode ser a condição na qual uma junção se baseia. Não há limite para o número de predicados que podem ser incluídos em uma condição de pesquisa. Para obter mais informações sobre predicados e condições de pesquisa, consulte Condição de pesquisa (Transact-SQL).

CORRENTE DE
Especifica que a atualização é executada na posição atual do cursor especificado.

Uma atualização posicionada usando uma cláusula WHERE CURRENT OF atualiza a única linha na posição atual do cursor. Isso pode ser mais preciso do que uma atualização pesquisada que usa uma cláusula WHERE <search_condition> para qualificar as linhas a serem atualizadas. Uma atualização pesquisada modifica várias linhas quando a condição de pesquisa não identifica exclusivamente uma única linha.

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 existirem um cursor global e um cursor local com o nome cursor_name , este argumento refere-se ao cursor global se GLOBAL for especificado; caso contrário, refere-se 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 permita atualizações.

OPÇÃO (<query_hint> [ ,... n ] )
Especifica que as dicas do otimizador são usadas para personalizar a maneira como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte Sugestões de Consulta (Transact-SQL).

Melhores práticas

Use a @@ROWCOUNT função para retornar o número de linhas inseridas para o aplicativo cliente. Para obter mais informações, consulte @@ROWCOUNT (Transact-SQL).

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

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

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

Suporte de compatibilidade

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

Tipos de dados

Todas as colunas char e nchar são acolchoadas à direita no comprimento definido.

Se ANSI_PADDING estiver definido como OFF, todos os espaços à direita serão removidos dos dados inseridos nas 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 ATIVADO, os espaços à direita serão inseridos. O driver ODBC do Microsoft SQL Server e o provedor OLE DB para SQL Server definem automaticamente ANSI_PADDING ON para cada conexão. Isso pode ser configurado em fontes de dados ODBC ou definindo atributos ou propriedades de conexão. Para obter mais informações, consulte SET ANSI_PADDING (Transact-SQL).

Atualizando colunas de texto, ntext e imagem

Modificar uma coluna de texto, ntext ou imagem com UPDATE inicializa a coluna, atribui um ponteiro de texto válido a ela e aloca pelo menos uma página de dados, a menos que a coluna esteja sendo atualizada com NULL.

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

Se a instrução UPDATE puder alterar mais de uma linha durante a atualização da chave de clustering e de uma ou mais colunas de texto, ntext ou imagem , a atualização parcial dessas colunas será executada como uma substituição completa dos valores.

Important

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 novos trabalhos de desenvolvimento e planeje modificar aplicativos que os usam atualmente. Use nvarchar(max), varchar(max)e varbinary(max) em vez disso.

Atualização de tipos de dados de grande valor

Utilize a seringa .Cláusula WRITE (expression,Offset,Length@@) para executar uma atualização parcial ou total dos tipos de dados varchar(max), nvarchar(max) e varbinary(max).

Por exemplo, uma atualização parcial de uma coluna varchar(max) pode excluir ou modificar apenas os primeiros 200 bytes da coluna (200 caracteres se usar caracteres ASCII), enquanto uma atualização completa excluiria ou modificaria todos os dados na coluna. . As atualizações WRITE que inserem ou acrescentam novos dados são minimamente registradas se o modelo de recuperação de banco de dados estiver definido como bulk-logged ou simples. O registo mínimo não é utilizado quando os valores existentes são atualizados. Para obter mais informações, consulte O log de transações (SQL Server).

O Mecanismo de Banco de Dados converte uma atualização parcial em uma atualização completa quando a instrução UPDATE causa uma destas ações:

• Altera uma coluna de chave do modo de exibição ou tabela particionado.
• Modifica mais de uma linha e também atualiza a chave de um índice clusterizado não exclusivo para um valor não constante.

Não é possível usar o arquivo . Cláusula WRITE para atualizar uma coluna NULL ou definir o valor de column_name como NULL.

@ Offset e @Length são especificados em bytes para os tipos de dados varbinary e varchar e em pares de bytes para o tipo de dados nvarchar . Para obter mais informações sobre comprimentos de tipo de dados de cadeia de caracteres, consulte char e varchar (Transact-SQL) e nchar e nvarchar (Transact-SQL).

Para obter o melhor desempenho, recomendamos que os dados sejam inseridos ou atualizados em tamanhos de bloco que sejam múltiplos de 8040 bytes.

Se a coluna modificada pelo . A cláusula WRITE é referenciada em uma cláusula OUTPUT, o valor completo da coluna, ou a imagem antes em excluída.column_name ou a imagem posterior inserida.column_name, é retornado para a coluna especificada na variável de tabela. Veja o exemplo R a seguir.

Para obter a mesma funcionalidade do . ESCREVA com outros tipos de dados binários ou de caracteres, use o STUFF (Transact-SQL).

Atualizando colunas Tipo definido pelo usuário

A atualização de valores em colunas de tipo definidas pelo usuário pode ser realizada de uma das seguintes maneiras:

• Fornecer um valor em um tipo de dados de sistema do SQL Server, desde que o tipo definido pelo usuário ofereça suporte à conversão implícita ou explícita desse tipo. O exemplo a seguir mostra como atualizar um valor em uma coluna do tipo Pointdefinido pelo usuário , convertendo explicitamente de uma cadeia de caracteres.

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

• Invocando um método, marcado como um mutador, do tipo definido pelo usuário, para executar a atualização. O exemplo a seguir invoca um método mutador do tipo Point chamado SetXY. Isso atualiza o estado da instância do tipo.

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

  Note

  O SQL Server retornará um erro se um método mutador for invocado em um valor nulo Transact-SQL ou se um novo valor produzido por um método mutador for nulo.

• Modificar o valor de uma propriedade registrada ou membro de dados públicos do tipo definido pelo usuário. A expressão que fornece o valor deve ser implicitamente convertível para o tipo do imóvel. O exemplo a seguir modifica o valor da propriedade X do tipo Pointdefinido pelo usuário.

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

  Para modificar propriedades diferentes da mesma coluna de tipo definida pelo usuário, emita várias instruções UPDATE ou invoque um método mutador do tipo.

Atualizando dados FILESTREAM

Você pode usar a instrução UPDATE para atualizar um campo FILESTREAM para um valor nulo, valor vazio ou uma quantidade relativamente pequena de dados embutidos. No entanto, uma grande quantidade de dados é transmitida de forma mais eficiente para um arquivo usando interfaces Win32. Ao atualizar um campo FILESTREAM, você modifica os dados BLOB subjacentes no sistema de arquivos. Quando um campo FILESTREAM é definido como NULL, os dados BLOB associados ao campo são excluídos. Você não pode usar o . WRITE(), para executar atualizações parciais nos dados FILESTREAM. Para obter mais informações, consulte FILESTREAM (SQL Server).

Tratamento de erros

Se uma atualização para uma linha violar uma restrição ou regra, violar a configuração NULL para a coluna ou 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 para uma coluna ou colunas que participam de um índice clusterizado fizer com que o tamanho do índice clusterizado e da linha exceda 8.060 bytes, a atualização falhará e uma mensagem de erro será retornada.

Interoperability

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

Quando um INSTEAD OF gatilho é definido em ações UPDATE em uma tabela, o gatilho está sendo executado em vez da instrução UPDATE. As versões anteriores do SQL Server oferecem suporte apenas a gatilhos AFTER definidos em UPDATE e outras instruções de modificação de dados. A cláusula FROM não pode ser especificada em uma instrução UPDATE que faz referência, direta ou indiretamente, a uma exibição com um INSTEAD OF gatilho definido nela. Para obter mais informações sobre INSTEAD OF triggers, consulte CREATE TRIGGER (Transact-SQL).

Atualmente, a cláusula FROM não pode ser especificada em uma instrução UPDATE no Warehouse no Microsoft Fabric. Há suporte para instruções UPDATE de tabela única.

Limitações e restrições

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

Quando uma expressão de tabela comum (CTE) é o destino de uma instrução UPDATE, todas as referências à CTE na instrução devem corresponder. Por exemplo, se for atribuído ao CTE um alias na cláusula FROM, o alias deverá ser usado para todas as outras referências ao CTE. Referências CTE inequívocas são necessárias porque uma CTE não tem uma ID de objeto, que o SQL Server usa para reconhecer a relação implícita entre um objeto e seu alias. Sem essa relação, o plano de consulta pode produzir um comportamento de junção inesperado e resultados de consulta não intencionais. Os exemplos a seguir demonstram métodos corretos e incorretos de especificar um CTE quando o CTE é 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 com referências CTE que são incorretamente correspondidas.

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  

Aqui está o conjunto de resultados.

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

Comportamento de bloqueio

Uma instrução UPDATE adquire um bloqueio (X) exclusivo em todas as linhas que modifica e mantém esses bloqueios até que a transação seja concluída. Dependendo do plano de consulta para a instrução UPDATE, do número de linhas que estão sendo modificadas e do nível de isolamento da transação, os bloqueios podem ser adquiridos no nível da página ou da tabela, em vez do nível da linha. Para evitar esses bloqueios de nível mais alto, considere dividir instruções de atualização que afetam milhares de linhas ou mais em lotes e garantir que todas as condições de junção e filtro sejam suportadas por índices. Consulte o artigo sobre Bloqueio no Mecanismo de Banco de Dados para obter mais detalhes sobre a mecânica de bloqueio no SQL Server.

Se o bloqueio otimizado estiver habilitado, alguns aspetos do comportamento de bloqueio serão UPDATE alterados. Por exemplo, bloqueios exclusivos (X) não são mantidos até que a transação seja concluída. Para obter mais informações, consulte Bloqueio otimizado.

Comportamento de registo

A instrução UPDATE é registrada; no entanto, atualizações parciais para tipos de dados de grande valor usando o . A cláusula WRITE é minimamente registrada. Para obter mais informações, consulte "Atualizando tipos de dados de grande valor" na seção anterior "Tipos de dados".

Segurança

Permissions

UPDATE As permissões são necessárias na tabela de destino. SELECT também são necessárias permissões para a tabela que está sendo atualizada se a instrução UPDATE contiver uma cláusula WHERE ou se a expressão na cláusula SET usar uma coluna na tabela.

Permissões UPDATE padrão para membros da sysadmin função de servidor fixa, as funções de banco de dados fixas db_ownerdb_datawriter e e o proprietário da tabela. Os membros do sysadmin, db_ownere db_securityadmin funções, e o proprietário da tabela podem transferir permissões para outros usuários.

Examples

Category	Elementos de sintaxe em destaque
Sintaxe básica	UPDATE
Limitando as linhas que são atualizadas	ONDE * TOPO * COM expressão de tabela comum * ONDE CORRENTE DE
Definindo valores de coluna	valores computados * operadores compostos * valores padrão * subconsultas
Especificando objetos de destino diferentes de tabelas padrão	visualizações * variáveis de tabela * aliases de tabela
Atualizando dados com base em dados de outras tabelas	FROM
Atualizando linhas em uma tabela remota	servidor vinculado * OPENQUERY * OPENDATASOURCE
Atualizando tipos de dados de objetos grandes	. ESCREVER * OPENROWSET
Atualizando tipos definidos pelo usuário	Tipos definidos pelo utilizador
Substituindo o comportamento padrão do otimizador de consulta usando dicas	Dicas de tabela * Dicas de consulta
Capturando os resultados da declaração UPDATE	Cláusula OUTPUT
Usando UPDATE em outras instruções	Procedimentos armazenados * TRY... CAPTURAS

Sintaxe básica

Exemplos nesta seção demonstram a funcionalidade básica da instrução UPDATE usando a sintaxe mínima necessária.

A. Usando uma instrução UPDATE simples

O exemplo a seguir atualiza uma única coluna para todas as linhas da Person.Address tabela.

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

B. Atualizando várias colunas

O exemplo a Bonusseguir atualiza os valores nas colunas , CommissionPcte SalesQuota para todas as linhas da SalesPerson tabela.

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

Limitando as linhas que são atualizadas

Os exemplos nesta seção demonstram maneiras que você pode usar para limitar o número de linhas afetadas pela instrução UPDATE.

C. Utilização da cláusula WHERE

O exemplo a seguir usa a cláusula WHERE para especificar quais linhas devem ser atualizadas. A instrução atualiza o valor na Color coluna da Production.Product tabela para todas as linhas que têm um valor existente de 'Vermelho' na Color coluna e têm um valor na Name coluna que começa com '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. Utilização da cláusula TOP

Os exemplos a seguir usam a cláusula TOP para limitar o número de linhas que são modificadas em uma instrução UPDATE. Quando uma cláusula TOP (n) é usada com UPDATE, a operação de atualização é executada em uma seleção aleatória de 'n' número de linhas. O exemplo a seguir atualiza a VacationHours coluna em 25% para 10 linhas aleatórias na Employee tabela.

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

Se você precisar usar TOP para aplicar atualizações em uma cronologia significativa, deverá usar TOP junto com ORDER BY em uma instrução subselect. O exemplo a seguir atualiza as horas de férias dos 10 funcionários com as primeiras datas de contratação.

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. Usando a cláusula WITH common_table_expression

O exemplo a seguir atualiza o PerAssemblyQty valor de todas as partes e componentes usados direta ou indiretamente para criar o ProductAssemblyID 800. A expressão de tabela comum retorna uma lista hierárquica de partes que são usadas diretamente para construir ProductAssemblyID 800 e partes que são usadas para criar esses componentes, e assim por diante. Somente as linhas retornadas pela expressão de tabela comum são modificadas.

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. Utilização da cláusula WHERE CURRENT OF

O exemplo a seguir usa a cláusula WHERE CURRENT OF para atualizar apenas a linha na qual o cursor está posicionado. Quando um cursor é baseado em uma associação, somente o table_name especificado na instrução UPDATE é modificado. Outras tabelas que participam do cursor não são afetadas.

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  

Definindo valores de coluna

Os exemplos nesta seção demonstram a atualização de colunas usando valores computados, subconsultas e valores DEFAULT.

G. Especificando um valor calculado

Os exemplos a seguir usam valores computados em uma instrução UPDATE. O exemplo dobra o valor na ListPrice coluna para todas as linhas da Product tabela.

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

H. Especificando um operador composto

O exemplo a seguir usa a variável @NewPrice para incrementar o preço de todas as bicicletas vermelhas pegando o preço atual e adicionando 10 a ele.

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

O exemplo a seguir usa o operador composto += para acrescentar os dados ' - tool malfunction' ao valor existente na coluna Name para linhas que têm um ScrapReasonID entre 10 e 12.

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

I. Especificando uma subconsulta na cláusula SET

O exemplo a seguir usa uma subconsulta na cláusula SET para determinar o valor usado para atualizar a coluna. A subconsulta deve retornar apenas um valor escalar (ou seja, um único valor por linha). O exemplo modifica a SalesYTD coluna na SalesPerson tabela para refletir as vendas mais recentes registradas na SalesOrderHeader tabela. A subconsulta agrega as vendas de cada vendedor no UPDATE extrato.

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. Atualizando linhas usando valores DEFAULT

O exemplo a seguir define a CostRate coluna como seu valor padrão (0,00) para todas as linhas que têm um CostRate valor maior que 20.00.

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

Especificando objetos de destino diferentes de tabelas padrão

Os exemplos nesta seção demonstram como atualizar linhas especificando um modo de exibição, alias de tabela ou variável de tabela.

K. Especificando um modo de exibição como o objeto de destino

O exemplo a seguir atualiza linhas em uma tabela especificando um modo de exibição como o objeto de destino. A definição de exibição faz referência a várias tabelas, no entanto, a instrução UPDATE é bem-sucedida porque faz referência a colunas de apenas uma das tabelas subjacentes. A instrução UPDATE falharia se as colunas de ambas as tabelas fossem especificadas. Para obter mais informações, consulte Modificar dados por meio de um modo de exibição.

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

L. Especificando um alias de tabela como o objeto de destino

O exemplo a seguir atualiza linhas na tabela Production.ScrapReason. O alias de tabela atribuído na ScrapReason cláusula FROM é especificado como o objeto de destino na cláusula 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. Especificando uma variável de tabela como o objeto de destino

O exemplo a seguir atualiza linhas em uma variável de tabela.

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  

Atualizando dados com base em dados de outras tabelas

Exemplos nesta seção demonstram métodos de atualização de linhas de uma tabela com base em informações em outra tabela.

N. Usando a instrução UPDATE com informações de outra tabela

O exemplo a seguir modifica a SalesYTD coluna na SalesPerson tabela para refletir as vendas mais recentes registradas na SalesOrderHeader tabela.

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  

O exemplo anterior pressupõe que apenas uma venda é registrada para um vendedor especificado em uma data específica e que as atualizações são atuais. Se mais de uma venda para um vendedor especificado puder ser registrada no mesmo dia, o exemplo mostrado não funcionará corretamente. O exemplo é executado sem erros, mas cada SalesYTD valor é atualizado com apenas uma venda, independentemente de quantas vendas realmente ocorreram naquele dia. Isso ocorre porque uma única instrução UPDATE nunca atualiza a mesma linha duas vezes.

Na situação em que mais de uma venda para um determinado vendedor pode ocorrer no mesmo dia, todas as vendas de cada vendedor devem ser agregadas dentro da UPDATE declaração, como mostrado no exemplo a seguir:

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  

Atualizando linhas em uma tabela remota

Os exemplos nesta seção demonstram como atualizar linhas em uma tabela de destino remoto usando um servidor vinculado ou uma função de conjunto de linhas para fazer referência à tabela remota.

O. Atualizando dados em uma tabela remota usando um servidor vinculado

O exemplo a seguir atualiza uma tabela em um servidor remoto. O exemplo começa criando um link para a fonte de dados remota usando sp_addlinkedserver. O nome do servidor vinculado, MyLinkedServer, é então especificado como parte do nome do objeto de quatro partes no formato server.catalog.schema.object. Observe que você deve especificar um nome de servidor válido para @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. Atualizando dados em uma tabela remota usando a função OPENQUERY

O exemplo a seguir atualiza uma linha em uma tabela remota especificando a função de conjunto de linhas OPENQUERY . O nome do servidor vinculado criado no exemplo anterior é usado neste exemplo.

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

Q. Atualizando dados em uma tabela remota usando a função OPENDATASOURCE

O exemplo a seguir atualiza uma linha em uma tabela remota especificando a função de conjunto de linhas OPENDATASOURCE . Especifique um nome de servidor válido para a fonte de dados usando o formato server_name ou server_name\instance_name. Talvez seja necessário configurar a instância do SQL Server para consultas distribuídas ad hoc. Para obter mais informações, consulte Opção de configuração do servidor de consultas distribuídas ad hoc.

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

Atualizando tipos de dados de objeto grande

Exemplos nesta seção demonstram métodos de atualização de valores em colunas que são definidas com tipos de dados de objeto grande (LOB).

R. Usando UPDATE com . WRITE para modificar dados em uma coluna nvarchar(max)

O exemplo a seguir usa o arquivo . Cláusula WRITE para atualizar um valor parcial em DocumentSummary, uma coluna nvarchar(max) na Production.Document tabela. A palavra components é substituída pela palavra features especificando a palavra de substituição, a localização inicial (deslocamento) da palavra a substituir nos dados existentes e o número de carateres a substituir (comprimento). O exemplo também usa a cláusula OUTPUT para retornar as imagens antes e depois da DocumentSummary coluna para a @MyTableVar variável table.

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. Usando UPDATE com . WRITE para adicionar e remover dados em uma coluna nvarchar(max)

Os exemplos a seguir adicionam e removem dados de uma coluna nvarchar(max) que tem um valor atualmente definido como NULL. Porque o . A cláusula WRITE não pode ser usada para modificar uma coluna NULL, a coluna é preenchida primeiro com dados temporários. Esses dados são então substituídos pelos dados corretos usando o arquivo . Cláusula WRITE. Os exemplos adicionais acrescentam dados ao final do valor da coluna, removem (truncam) dados da coluna e, finalmente, removem dados parciais da coluna. As instruções SELECT exibem a modificação de dados gerada por cada instrução 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. Usando UPDATE com OPENROWSET para modificar uma coluna varbinary(max)

O exemplo a seguir substitui uma imagem existente armazenada em uma coluna varbinary(max) por uma nova imagem. A função OPENROWSET é usada com a opção BULK para carregar a imagem na coluna. Este exemplo pressupõe que um arquivo nomeado Tires.jpg existe no caminho de arquivo especificado.

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

U. Usando UPDATE para modificar dados FILESTREAM

O exemplo a seguir usa a instrução UPDATE para modificar os dados no arquivo de sistema de arquivos. Não recomendamos esse método para transmitir grandes quantidades de dados para um arquivo. Use as interfaces Win32 apropriadas. O exemplo a seguir substitui qualquer texto no registro de arquivo pelo texto Xray 1. Para obter mais informações, consulte FILESTREAM (SQL Server).

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

Atualizando tipos definidos pelo usuário

Os exemplos a seguir modificam valores em colunas de tipo definido pelo usuário (UDT) CLR. São demonstrados três métodos. Para obter mais informações sobre colunas definidas pelo usuário, consulte Tipos de User-Defined CLR.

V. Usando um tipo de dados do sistema

Você pode atualizar um UDT fornecendo um valor em um tipo de dados de sistema do SQL Server, desde que o tipo definido pelo usuário ofereça suporte à conversão implícita ou explícita desse tipo. O exemplo a seguir mostra como atualizar um valor em uma coluna do tipo Pointdefinido pelo usuário , convertendo explicitamente de uma cadeia de caracteres.

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

W. Invocando um método

Você pode atualizar um UDT invocando um método, marcado como um mutador, do tipo definido pelo usuário, para executar a atualização. O exemplo a seguir invoca um método mutador do tipo Point chamado SetXY. Isso atualiza o estado da instância do tipo.

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

X. Modificando o valor de uma propriedade ou membro de dados

Você pode atualizar um UDT modificando o valor de uma propriedade registrada ou membro de dados públicos do tipo definido pelo usuário. A expressão que fornece o valor deve ser implicitamente convertível para o tipo do imóvel. O exemplo a seguir modifica o valor da propriedade X do tipo Pointdefinido pelo usuário.

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

Substituindo o comportamento padrão do otimizador de consulta usando dicas

Os exemplos nesta seção demonstram como usar dicas de tabela e consulta para substituir temporariamente o comportamento padrão do otimizador de consulta ao processar a instrução UPDATE.

Caution

Como o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, recomendamos que as dicas sejam usadas apenas como último recurso por desenvolvedores e administradores de banco de dados experientes.

Y. Especificando uma dica de tabela

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

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

Z. Especificando uma dica de consulta

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

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

Capturando os resultados da instrução UPDATE

Exemplos nesta seção demonstram como usar a cláusula OUTPUT para retornar informações ou expressões baseadas em cada linha afetada por uma instrução UPDATE. Esses resultados podem ser retornados ao aplicativo de processamento para uso em coisas como mensagens de confirmação, arquivamento e outros requisitos do aplicativo.

AA. Usando UPDATE com a cláusula OUTPUT

O exemplo a seguir atualiza a VacationHours coluna Employee na tabela em 25% para Funcionários com menos de 10 VacationHours e também define o valor na coluna ModifiedDate para a data atual. A OUTPUT cláusula retorna o valor do que existe antes de VacationHours aplicar a UPDATE instrução na deleted.VacationHours coluna e o valor atualizado na inserted.VacationHours coluna à @MyTableVar variável de tabela.

Seguem-se duas SELECT instruções que retornam os valores e @MyTableVar os resultados da operação de atualização na Employee tabela. Para obter mais exemplos usando a cláusula OUTPUT, consulte Cláusula 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  

Usando UPDATE em outras instruções

Exemplos nesta seção demonstram como usar UPDATE em outras instruções.

AB. Usando UPDATE em um procedimento armazenado

O exemplo a seguir usa uma instrução UPDATE em um procedimento armazenado. O procedimento usa um parâmetro de entrada, @NewHourse um parâmetro @RowCountde saída. O @NewHours valor do parâmetro é usado na instrução UPDATE para atualizar a coluna VacationHours na tabela HumanResources.Employee. O @RowCount parâmetro output é usado para retornar o número de linhas afetadas a uma variável local. A expressão CASE é usada na cláusula SET para determinar condicionalmente o valor definido para VacationHours. Quando o empregado é pago por hora (SalariedFlag = 0), VacationHours é definido como o número atual de horas mais o valor especificado em @NewHours; caso contrário, VacationHours é definido como o valor especificado em @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. Usando UPDATE em uma TRY... Bloco CATCH

O exemplo a seguir usa uma instrução UPDATE em um TRY... Bloco CATCH para lidar com erros de execução que podem ocorrer durante a operação de atualização.

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  

Exemplos: Azure Synapse Analytics and Analytics Platform System (PDW)

AD. Usando uma instrução UPDATE simples

Os exemplos a seguir mostram como todas as linhas podem ser afetadas quando uma cláusula WHERE não é usada para especificar a linha (ou linhas) a serem atualizadas.

Este exemplo atualiza os valores nas EndDate colunas e CurrentFlag para todas as linhas da DimEmployee tabela.

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

Você também pode usar valores computados em uma instrução UPDATE. O exemplo a ListPrice seguir dobra o valor na coluna para todas as linhas da Product tabela.

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

AE. Usando a instrução UPDATE com uma cláusula WHERE

O exemplo a seguir usa a cláusula WHERE para especificar quais linhas devem ser atualizadas.

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

AF. Usando a instrução UPDATE com rótulo

O exemplo a seguir mostra o uso de um LABEL para a instrução UPDATE.

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

AG. Usando a instrução UPDATE com informações de outra tabela

Este exemplo cria uma tabela para armazenar o total de vendas por ano. Ele atualiza o total de vendas para o ano de 2004 executando uma instrução SELECT na tabela 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. Adesão ANSI para declarações de atualização

Este exemplo mostra como atualizar dados com base no resultado da junção de outra tabela.

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

Ver também

CRIAR TABELA (Transact-SQL)
CRIAR GATILHO (Transact-SQL)
Cursores (Transact-SQL)
SUPRIMIR (Transact-SQL)
INSERIR (Transact-SQL)
Funções de texto e imagem (Transact-SQL)
COM common_table_expression (Transact-SQL)
FILESTREAM (SQL Server)
Suporte a agrupamento e Unicode
Single-Byte e conjuntos de caracteres multibyte