UPDATE (Transact-SQL)UPDATE (Transact-SQL)

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2008)simBanco de Dados SQL do Microsoft AzuresimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Altera dados existentes em uma tabela ou exibição no SQL Server 2017SQL Server 2017.Changes existing data in a table or view in SQL Server 2017SQL Server 2017. Para obter exemplos, confira Exemplos.For examples, see Examples.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

-- 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 SQL Data Warehouse and 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 ) ]  
[;]  

ArgumentosArguments

WITH <common_table_expression>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.Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement. O conjunto de resultados da CTE é derivado de uma consulta simples e é referido pela instrução UPDATE.The CTE result set is derived from a simple query and is referenced by UPDATE statement.

Expressões de tabela comuns também podem ser usadas com as instruções SELECT, INSERT, DELETE e CREATE VIEW.Common table expressions can also be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements. Para obter mais informações, confira WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression) [ PERCENT ]TOP ( expression) [ PERCENT ]
Especifica o número ou o percentual de linhas atualizadas.Specifies the number or percent of rows that are updated. expression pode ser um número ou uma porcentagem das linhas.expression can be either a number or a percent of the rows.

As linhas referenciadas na expressão TOP usada com INSERT, UPDATE ou DELETE não são organizadas em qualquer ordem.The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Os parênteses delimitando a expressão em TOP são necessários em instruções INSERT, UPDATE e DELETE.Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements. Para obter mais informações, confira TOP (Transact-SQL).For more information, see TOP (Transact-SQL).

table_aliastable_alias
O alias especificado na cláusula FROM que representa a tabela ou exibição na qual as linhas devem ser atualizadas.The alias specified in the FROM clause representing the table or view from which the rows are to be updated.

server_nameserver_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 a exibição está localizada.Is the name of the server (using a linked server name or the OPENDATASOURCE function as the server name) on which the table or view is located. Se server_name for especificado, database_name e schema_name serão necessários.If server_name is specified, database_name and schema_name are required.

database_namedatabase_name
É o nome do banco de dados.Is the name of the database.

schema_nameschema_name
É o nome do esquema ao qual a tabela ou exibição pertence.Is the name of the schema to which the table or view belongs.

table_or_view_nametable_or_view_name
É o nome da tabela ou exibição na qual as linhas serão atualizadas.Is the name of the table or view from which the rows are to be updated. A exibição referenciada por table_or_view_name precisa ser atualizável e referenciar exatamente uma tabela base na cláusula FROM da exibição.The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limitedrowset_function_limited
É a função OPENQUERY ou OPENROWSET, sujeita aos recursos do provedor.Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities.

WITH ( <Table_Hint_Limited> )WITH ( <Table_Hint_Limited> )
Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino.Specifies one or more table hints that are allowed for a target table. A palavra-chave WITH e parênteses são necessários.The WITH keyword and the parentheses are required. NOLOCK e READUNCOMMITTED não são permitidos.NOLOCK and READUNCOMMITTED are not allowed. Para obter informações sobre dicas de tabela, confira Dicas de tabela (Transact-SQL).For information about table hints, see Table Hints (Transact-SQL).

@table_variable@table_variable
Especifica uma variável table como uma origem de tabela.Specifies a table variable as a table source.

SETSET
Especifica a lista de colunas ou nomes de variáveis a serem atualizados.Specifies the list of column or variable names to be updated.

column_namecolumn_name
É uma coluna que contém os dados a serem alterados.Is a column that contains the data to be changed. column_name precisa existir em table_or view_name.column_name must exist in table_or view_name. Colunas de identidade não podem ser atualizadas.Identity columns cannot be updated.

expressãoexpression
É uma variável, valor literal, expressão ou uma instrução de subseleção (incluída com parênteses) que retorna um único valor.Is a variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value. O valor retornado pela expressão substituirá o valor existente em column_name ou em @variable.The value returned by expression replaces the existing value in column_name or @variable.

Observação

Ao referenciar os tipos de dados de caractere Unicode nchar, nvarchar e ntext, 'expression' deve ter a letra maiúscula 'N' como prefixo.When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'. Se N não for especificado, o SQL ServerSQL Server converte a cadeia na página de código correspondente ao agrupamento padrão do banco de dados ou coluna.If 'N' is not specified, SQL ServerSQL Server converts the string to the code page that corresponds to the default collation of the database or column. Qualquer caractere não localizado nessa página de código será perdido.Any characters not found in this code page are lost.

DEFAULTDEFAULT
Especifica que o valor padrão definido para a coluna deve substituir o valor existente na coluna.Specifies that the default value defined for the column is to replace the existing value in the column. 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.This can also be used to change the column to NULL if the column has no default and is defined to allow null values.

{ += | -= | *= | /= | %= | &= | ^= | |= }{ += | -= | *= | /= | %= | &= | ^= | |= }
Operador de atribuição composto:Compound assignment operator:
+= Adicionar e atribuir+= Add and assign
-= Subtrair e atribuir-= Subtract and assign
*= Multiplicar e atribuir*= Multiply and assign
/= Dividir e atribuir/= Divide and assign
%= Módulo e atribuir%= Modulo and assign
&= AND bit a bit e atribuir&= Bitwise AND and assign
^= XOR bit a bit e atribuir^= Bitwise XOR and assign
|= OR bit a bit e atribuir|= Bitwise OR and assign

udt_column_nameudt_column_name
É uma coluna de tipo definido pelo usuário.Is a user-defined type column.

property_name | field_nameproperty_name | field_name
É uma propriedade pública ou membro de dados público de um tipo definido pelo usuário.Is a public property or public data member of a user-defined type.

method_name ( argument [ ,... n] )method_name ( argument [ ,... n] )
É um método modificador público não estático de udt_column_name que usa um ou mais argumentos.Is a nonstatic public mutator method of udt_column_name that takes one or more arguments.

. WRITE (expressão,@Offset,@Length). WRITE (expression,@Offset,@Length)
Especifica que uma seção do valor de column_name deve ser modificada.Specifies that a section of the value of column_name is to be modified. A expressão substitui as unidades de @Length de @Offset do column_name.expression replaces @Length units starting from @Offset of column_name. Somente colunas de varchar(max), nvarchar(max) ou varbinary(max) podem ser especificadas com esta cláusula.Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. column_name não pode ser NULL e não pode ser qualificado com um nome de tabela nem com um alias de tabela.column_name cannot be NULL and cannot be qualified with a table name or table alias.

A expressão é o valor que é copiado para column_name.expression is the value that is copied to column_name. A expressão precisa ser avaliada ou ter a capacidade de ser convertida implicitamente no tipo column_name.expression must evaluate to or be able to be implicitly cast to the column_name type. Se a expressão for definida como NULL, @Length será ignorado e o valor em column_name será truncado no @Offset especificado.If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.

@Offset é o ponto inicial no valor de column_name no qual a expressão é escrita.@Offset is the starting point in the value of column_name at which expression is written. @Offset é uma posição ordinal com base em zero, é bigint e não pode ser um número negativo.@Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. Se @Offset for NULL, a operação de atualização acrescentará a expressão ao final do valor de column_name existente e @Length será ignorado.If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. Se @Offset for maior que o comprimento do valor de column_name, o Mecanismo de Banco de DadosDatabase Engine retornará um erro.If @Offset is greater than the length of the column_name value, the Mecanismo de Banco de DadosDatabase Engine returns an error. Se @Offset mais @Length exceder o final do valor subjacente na coluna, a exclusão ocorrerá até o último caractere do valor.If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value. Se @Offset mais LEN (expressão) for maior do que o tamanho subjacente declarado, ocorrerá um erro.If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.

@Length é o comprimento da seção na coluna, começando em @Offset, que é substituído pela expressão.@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length é bigint e não pode ser um número negativo.@Length is bigint and cannot be a negative number. Se @Length for NULL, a operação de atualização removerá todos os dados de @Offset até final do valor de column_name.If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

Para obter mais informações, consulte Comentários.For more information, see Remarks.

@ variable@ variable
É uma variável declarada definida como o valor retornado pela expressão.Is a declared variable that is set to the value returned by expression.

SET @variable = column = expression define a variável com o mesmo valor que a coluna.SET @variable = column = expression sets the variable to the same value as the column. Isso é diferente de SET @variable = column, column = expression, que define a variável para o valor de pré-atualização da coluna.This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

<OUTPUT_Clause><OUTPUT_Clause>
Retorna dados atualizados ou expressões com base neles, como parte da operação UPDATE.Returns updated data or expressions based on it as part of the UPDATE operation. A cláusula OUTPUT não tem suporte em nenhuma instrução DML destinada a exibições ou tabelas remotas.The OUTPUT clause is not supported in any DML statements that target remote tables or views. Para obter mais informações, confira Cláusula OUTPUT (Transact-SQL).For more information, see OUTPUT Clause (Transact-SQL).

FROM <table_source>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.Specifies that a table, view, or derived table source is used to provide the criteria for the update operation. Para obter mais informações, consulte FROM (Transact-SQL).For more information, see 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.If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. 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.If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. Todas as outras referências ao objeto na cláusula FROM devem incluir um alias de objeto.All other references to the object in the FROM clause must include an object alias.

Uma exibição com um gatilho INSTEAD OF UPDATE não pode ser um destino de UPDATE com uma cláusula FROM.A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.

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.Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. 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.In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.

WHEREWHERE
Especifica os critérios que limitam as linhas que são atualizadas.Specifies the conditions that limit the rows that are updated. Há duas formas de atualização com base na forma em que a clausula WHERE é usada:There are two forms of update based on which form of the WHERE clause is used:

  • Atualizações pesquisadas especificam um critério de pesquisa para qualificar as linhas a serem excluídas.Searched updates specify a search condition to qualify the rows to delete.

  • Atualizações posicionadas usam a cláusula CURRENT OF para especificar um cursor.Positioned updates use the CURRENT OF clause to specify a cursor. A operação de atualização ocorre na posição atual do cursor.The update operation occurs at the current position of the cursor.

<search_condition><search_condition>
Especifica o critério a ser atendido para as linhas a serem atualizadas.Specifies the condition to be met for the rows to be updated. O critério de pesquisa também pode ser o critério no qual uma junção é baseada.The search condition can also be the condition upon which a join is based. Não há nenhum limite para o número de predicados que podem ser incluídos em um critério de pesquisa.There is no limit to the number of predicates that can be included in a search condition. Para obter mais informações sobre predicados e condições de pesquisa, confira Critério de pesquisa (Transact-SQL).For more information about predicates and search conditions, see Search Condition (Transact-SQL).

CURRENT OFCURRENT OF
Especifica que a atualização é executada na posição atual do cursor especificado.Specifies that the update is performed at the current position of the specified cursor.

Uma atualização posicionada usando uma cláusula WHERE CURRENT OF atualiza a única linha na posição atual do cursor.A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the 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.This can be more accurate than a searched update that uses a WHERE <search_condition> clause to qualify the rows to be updated. Uma atualização pesquisada modifica várias linhas quando o critério de pesquisa não identifica exclusivamente uma única linha.A searched update modifies multiple rows when the search condition does not uniquely identify a single row.

GLOBALGLOBAL
Especifica que cursor_name se refere a um cursor global.Specifies that cursor_name refers to a global cursor.

cursor_namecursor_name
É o nome do cursor aberto a partir do qual a busca deve ser feita.Is the name of the open cursor from which the fetch should be made. 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.If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. O cursor deve permitir atualizações.The cursor must allow updates.

cursor_variable_namecursor_variable_name
É o nome de uma variável de cursor.Is the name of a cursor variable. cursor_variable_name precisa fazer referência a um cursor que permita atualizações.cursor_variable_name must reference a cursor that allows updates.

OPTION ( <query_hint> [ ,... n ] )OPTION ( <query_hint> [ ,... n ] )
Especifica que dicas de otimização são usadas para personalizar a maneira como o Mecanismo de Banco de DadosDatabase Engine processa a instrução.Specifies that optimizer hints are used to customize the way the Mecanismo de Banco de DadosDatabase Engine processes the statement. Para obter mais informações, veja Dicas de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

Práticas recomendadasBest Practices

Use a função @@ROWCOUNT para retornar o número de linhas inseridas no aplicativo cliente.Use the @@ROWCOUNT function to return the number of inserted rows to the client application. Para obter mais informações, consulte @@ROWCOUNT (Transact-SQL).For more information, see @@ROWCOUNT (Transact-SQL).

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.Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. Se a instrução UPDATE afetar vários registros, para retornar os valores novos e antigos de cada registro, use a cláusula OUTPUT.If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause.

Tenha cuidado ao especificar a cláusula FROM para fornecer os critérios da operação de atualização.Use caution when specifying the FROM clause to provide the criteria for the update operation. 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, ou seja, se a instrução UPDATE não for determinística.The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic. Por exemplo, na instrução UPDATE no script a seguir, as duas linhas em Table1 atendem às qualificações da cláusula FROM na instrução UPDATE; mas não está definido qual linha da Table1 é usada para atualizar a linha na Table2.For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.

USE AdventureWorks2012;  
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 acontecer quando as cláusulas FROM e WHERE CURRENT OF forem combinadas.The same problem can occur when the FROM and WHERE CURRENT OF clauses are combined. No exemplo a seguir, as duas linhas na Table2 atendem às qualificações da cláusula FROM na instrução UPDATE.In the following example, both rows in Table2 meet the qualifications of the FROM clause in the UPDATE statement. Não está definido qual linha na Table2 será usada para atualizar a linha na Table1.It is undefined which row from Table2 is to be used to update the row in Table1.

USE AdventureWorks2012;  
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 compatibilidadeCompatibility Support

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 ServerSQL Server.Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL ServerSQL Server. Evite usar essas dicas nesse contexto em desenvolvimentos novos e planeje modificar aplicativos que as usam atualmente.Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

Tipos de dadosData Types

Todas as colunas char e nchar são preenchidas à direita com o comprimento definido.All char and nchar columns are right-padded to the defined length.

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.If ANSI_PADDING is set to OFF, all trailing spaces are removed from data inserted into varchar and nvarchar columns, except in strings that contain only spaces. Essas cadeias de caracteres são truncadas para uma cadeia de caracteres vazia.These strings are truncated to an empty string. Se ANSI_PADDING estiver definido como ON, serão inseridos espaços à direita.If ANSI_PADDING is set to ON, trailing spaces are inserted. O driver ODBC do Microsoft SQL Server e o OLE DB Provider for SQL Server definem automaticamente ANSI_PADDING ON para cada conexão.The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. Isso pode ser configurado em fontes de dados ODBC ou por meio da configuração de atributos ou propriedades de conexão.This can be configured in ODBC data sources or by setting connection attributes or properties. Para obter mais informações, veja SET ANSI_PADDING (Transact-SQL).For more information, see SET ANSI_PADDING (Transact-SQL).

Atualizando colunas text, ntext e imageUpdating text, ntext, and image Columns

Modificar uma coluna de 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 menos que a coluna esteja sendo atualizada com NULL.Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page, unless the column is being updated with NULL.

Para substituir ou modificar blocos grandes de dados de text, ntext ou image, use WRITETEXT ou UPDATETEXT em vez da instrução UPDATE.To replace or modify large blocks of text, ntext, or image data, use WRITETEXT or UPDATETEXT instead of the UPDATE statement.

Se a instrução UPDATE puder alterar mais de uma linha ao atualizar a chave de cluster e uma ou mais colunas de text, ntext ou image, a atualização parcial dessas colunas será executada como uma substituição completa dos valores.If the UPDATE statement could change more than one row while updating both the clustering key and one or more text, ntext, or image columns, the partial update to these columns is executed as a full replacement of the values.

Importante

Os tipos de dados ntext, text e image serão removidos em uma versão futura do MicrosoftMicrosoft SQL ServerSQL Server.The ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoft SQL ServerSQL Server. Evite usar esses tipos de dados em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que os utilizam atualmente.Avoid using these data types in new development work, and plan to modify applications that currently use them. Em vez disso, use nvarchar(max), varchar(max)e varbinary(max) .Use nvarchar(max), varchar(max), and varbinary(max) instead.

Atualizando tipos de dados de valor grandeUpdating Large Value Data Types

Use a cláusula . WRITE (expression, @Offset ***,@Length*) para executar uma atualização parcial ou completa dos tipos de dados **varchar(max), nvarchar(max) e varbinary(max).Use the . WRITE (expression, @Offset ***,@Length*) clause to perform a partial or full update of **varchar(max), nvarchar(max), and varbinary(max) data types. Por exemplo, talvez uma atualização parcial de uma coluna varchar(max) poderá excluir ou modificar somente os 200 primeiros caracteres da coluna, enquanto uma atualização completa excluirá ou modificará todos os dados na coluna.For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. As atualizações de . WRITE que inserem ou acrescentam novos dados serão registradas em log minimamente se o modelo de recuperação do banco de dados estiver definido como bulk-logged ou simples.. WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple. A criação mínima de log não é usada quando valores existentes são atualizados.Minimal logging is not used when existing values are updated. Para obter mais informações, consulte O log de transações (SQL Server).For more information, see The Transaction Log (SQL Server).

O Mecanismo de Banco de DadosDatabase Engine converte uma atualização parcial em uma atualização completa quando a instrução UPDATE provoca uma destas ações:The Mecanismo de Banco de DadosDatabase Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:

  • Altera uma coluna de chave da exibição ou tabela particionada.Changes a key column of the partitioned view or table.
  • Modifica mais de uma linha e também atualiza a chave de um índice clusterizado não exclusivo para um valor não constante.Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value.

Não é possível usar a cláusula . WRITE para atualizar uma coluna NULL nem para definir o valor de column_name como NULL.You cannot use the . WRITE clause to update a NULL column or set the value of column_name to NULL.

@Offset e @Length são especificados em bytes para os tipos de dados varbinary e varchar e em caracteres para o tipo de dados nvarchar.@Offset and @Length are specified in bytes for varbinary and varchar data types and in characters for the nvarchar data type. Os deslocamentos apropriados são computados para agrupamentos de DBCS (conjunto de caracteres de dois bytes).The appropriate offsets are computed for double-byte character set (DBCS) collations.

Para obter melhor desempenho, é recomendável que os dados sejam inseridos ou atualizados em tamanhos de blocos que sejam múltiplos de 8040 bytes.For best performance, we recommend that data be inserted or updated in chunk sizes that are multiples of 8040 bytes.

Se a coluna modificada pela cláusula . WRITE for referenciada em uma cláusula OUTPUT, o valor completo da coluna, na imagem anterior em deleted.column_name ou na imagem posterior em inserted.column_name, será retornado para a coluna especificada na variável de tabela.If the column modified by the . WRITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the table variable. Veja o exemplo R a seguir.See example R that follows.

Para obter a mesma funcionalidade de . WRITE com outros tipos de dados de caractere ou binários, use STUFF (Transact-SQL).To achieve the same functionality of . WRITE with other character or binary data types, use the STUFF (Transact-SQL).

Atualizando colunas de tipo definido pelo usuárioUpdating User-defined Type Columns

A atualização de valores em colunas de tipo definido pelo usuário pode ser realizada de uma das seguintes maneiras:Updating values in user-defined type columns can be accomplished in one of the following ways:

  • Fornecendo um valor em um tipo de dados do sistema SQL ServerSQL Server, contanto que o tipo definido pelo usuário ofereça suporte à conversão implícita ou explícita do referido tipo.Supplying a value in a SQL ServerSQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. O exemplo a seguir mostra como atualizar um valor em uma coluna de tipo definido pelo usuário Point, convertendo-o explicitamente de uma cadeia de caracteres:The following example shows how to update a value in a column of user-defined type Point, by explicitly converting from a string.

    UPDATE Cities  
    SET Location = CONVERT(Point, '12.3:46.2')  
    WHERE Name = 'Anchorage';  
    
  • Invocando um método, marcado como um modificador, do tipo definido pelo usuário, para executar a atualização.Invoking a method, marked as a mutator, of the user-defined type, to perform the update. O exemplo a seguir invoca um método modificador de tipo Point denominado SetXY.The following example invokes a mutator method of type Point named SetXY. Isso atualiza o estado da instância do tipo.This updates the state of the instance of the type.

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

    Observação

    O SQL ServerSQL Server retornará um erro se um método modificador for invocado com um valor nulo Transact-SQLTransact-SQL, ou se um novo valor produzido por um método modificador for nulo. SQL ServerSQL Server returns an error if a mutator method is invoked on a Transact-SQLTransact-SQL null value, or if a new value produced by a mutator method is null.

  • Modificando o valor de uma propriedade registrada ou membro de dados público do tipo definido pelo usuário.Modifying the value of a registered property or public data member of the user-defined type. A expressão que fornece o valor deve poder ser implicitamente convertida para o tipo da propriedade.The expression supplying the value must be implicitly convertible to the type of the property. O exemplo a seguir modifica o valor de propriedade X do tipo definido pelo usuário Point:The following example modifies the value of property X of user-defined type Point.

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

    Para modificar propriedades diferentes da mesma coluna do tipo definido pelo usuário, emita várias instruções UPDATE ou invoque um método modificador do tipo.To modify different properties of the same user-defined type column, issue multiple UPDATE statements, or invoke a mutator method of the type.

Atualizando dados de FILESTREAMUpdating FILESTREAM Data

Você pode usar a instrução UPDATE para atualizar um campo FILESTREAM para um valor nulo, um valor vazio ou uma quantidade relativamente pequena de dados embutidos.You can use the UPDATE statement to update a FILESTREAM field to a null value, empty value, or a relatively small amount of inline data. No entanto, uma quantidade grande de dados é transmitida de maneira mais eficiente para um arquivo usando interfaces Win32.However, a large amount of data is more efficiently streamed into a file by using Win32 interfaces. Ao atualizar um campo FILESTREAM, você modifica os dados BLOB subjacentes no sistema de arquivos.When you update a FILESTREAM field, you modify the underlying BLOB data in the file system. Quando um campo FILESTREAM é definido como NULL, os dados BLOB associados ao campo são excluídos.When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted. Não é possível usar .WRITE() para executar atualizações parciais em dados FILESTREAM.You cannot use .WRITE(), to perform partial updates to FILESTREAM data. Para obter mais informações, veja FILESTREAM (SQL Server).For more information, see FILESTREAM (SQL Server).

Tratamento de errosError Handling

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.If an update to a row violates a constraint or rule, violates the NULL setting for the column, or the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.

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.When an UPDATE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) during expression evaluation, the update is not performed. O restante do lote não é executado e uma mensagem de erro é retornada.The rest of the batch is not executed, and an error message is returned.

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.If an update to a column or columns participating in a clustered index causes the size of the clustered index and the row to exceed 8,060 bytes, the update fails and an error message is returned.

InteroperabilidadeInteroperability

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.UPDATE statements are allowed in the body of user-defined functions only if the table being modified is a table variable.

Quando um gatilho INSTEAD OF é definido em ações UPDADE em uma tabela, o gatilho é executado em vez da instrução UPDATE.When an INSTEAD OF trigger is defined on UPDATE actions against a table, the trigger is running instead of the UPDATE statement. Versões anteriores do SQL ServerSQL Server oferecem suporte apenas a gatilhos AFTER definidos na UPDATE e em outras instruções de modificação de dados.Earlier versions of SQL ServerSQL Server only support AFTER triggers defined on UPDATE and other data modification statements. 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.The FROM clause cannot be specified in an UPDATE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger defined on it. Para obter mais informações sobre gatilhos INSTEAD OF, confira CREATE TRIGGER (Transact-SQL).For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

Limitações e restriçõesLimitations and Restrictions

A cláusula FROM não pode ser especificada em uma instrução UPDATE que referencie, direta ou indiretamente, uma exibição que tenha um gatilho INSTEAD OF definido.The FROM clause cannot be specified in an UPDATE statement that references, either directly or indirectly, a view that has an INSTEAD OF trigger defined on it. Para obter mais informações sobre gatilhos INSTEAD OF, confira CREATE TRIGGER (Transact-SQL).For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

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.When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. 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.For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the 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 ServerSQL Server usa para reconhecer a relação implícita entre um objeto e seu alias.Unambiguous CTE references are required because a CTE does not have an object ID, which SQL ServerSQL Server uses to recognize the implicit relationship between an object and its alias. Sem essa relação, o plano de consulta pode gerar comportamento de junção inesperado e resultados de consulta não intencionais.Without this relationship, the query plan may produce unexpected join behavior and unintended query results. 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.The following examples demonstrate correct and incorrect methods of specifying a CTE when the CTE is the target object of the update operation.

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.Here is the result set.

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

Instrução UPDATE com referências à CTE (Expressão de Tabela Comum) correspondidas incorretamente.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.Here is the result set.

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

Comportamento de bloqueioLocking Behavior

Uma instrução UPDATE sempre adquire um bloqueio exclusivo (X) na tabela que modifica e mantém esse bloqueio até que a transação seja concluída.An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. Com um bloqueio exclusivo, nenhuma outra transação pode modificar dados.With an exclusive lock, no other transactions can modify data. Você pode especificar dicas de tabela para substituir esse comportamento padrão durante a instrução UPDATE especificando outro método de bloqueio; entretanto, é recomendável que as dicas só sejam usadas como último recurso por desenvolvedores experientes e administradores de bancos de dados.You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).For more information, see Table Hints (Transact-SQL).

Comportamento de logLogging Behavior

A instrução UPDATE é registrada em log, no entanto, as atualizações parciais de tipos de dados de valor grande que usam a cláusula . WRITE são registradas minimamente.The UPDATE statement is logged; however, partial updates to large value data types using the . WRITE clause are minimally logged. Para obter mais informações, consulte "Atualizando tipos de dados de valor grande" na seção anterior "Tipos de dados".For more information, see "Updating Large Value Data Types" in the earlier section “Data Types”.

SegurançaSecurity

PermissõesPermissions

São necessárias permissões UPDATE na tabela de destino.UPDATE permissions are required on the target table. As permissões SELECT também serão necessárias para a tabela que está sendo atualizada se a instrução UPDATE contiver uma cláusula WHERE ou se a expression na cláusula SET usar uma coluna na tabela.SELECT permissions are also required for the table being updated if the UPDATE statement contains a WHERE clause, or if expression in the SET clause uses a column in the table.

As permissões UPDATE assumem como padrão os membros da função de servidor fixa sysadmin, das funções de banco de dados fixas db_owner e db_datawriter e o proprietário da tabela.UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Os membros das funções sysadmin, db_owner e db_securityadmin e o proprietário da tabela podem transferir permissões a outros usuários.Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.

ExemplosExamples

CategoriaCategory Elementos de sintaxe em destaqueFeatured syntax elements
Sintaxe básicaBasic Syntax UPDATEUPDATE
Limitando as linhas que são atualizadasLimiting the Rows that Are Updated WHERE • TOP • WITH expressão de tabela comum • WHERE CURRENT OFWHERE • TOP • WITH common table expression • WHERE CURRENT OF
Definindo valores de colunaSetting Column Values valores computados • operadores compostos • valores padrão • subconsultascomputed values • compound operators • default values • subqueries
Especificando objetos de destino que não sejam de tabelas padrãoSpecifying Target Objects Other than Standard Tables exibições • variáveis de tabela • aliases de tabelaviews • table variables • table aliases
Atualizando dados com base em dados de outras tabelasUpdating Data Based on Data From Other Tables FROMFROM
Atualizando linhas em uma tabela remotaUpdating Rows in a Remote Table servidor vinculado • OPENQUERY • OPENDATASOURCElinked server • OPENQUERY • OPENDATASOURCE
Atualizando tipos de dados de objeto grandeUpdating Large Object Data Types .WRITE • OPENROWSET.WRITE • OPENROWSET
Atualizando tipos definidos pelo usuárioUpdating User-defined Types tipos definidos pelo usuáriouser-defined types
Substituindo o comportamento padrão do otimizador de consulta usando dicasOverriding the Default Behavior of the Query Optimizer by Using Hints dicas de tabela • dicas de consultatable hints • query hints
Capturando os resultados da instrução UPDATECapturing the Results of the UPDATE Statement cláusula OUTPUTOUTPUT clause
Usando UPDATE em outras instruçõesUsing UPDATE in Other Statements Procedimentos armazenados • TRY…CATCHStored Procedures • TRY…CATCH

Sintaxe básicaBasic Syntax

Os exemplos nesta seção demonstram a funcionalidade básica da instrução UPDATE usando a sintaxe mínima necessária.Examples in this section demonstrate the basic functionality of the UPDATE statement using the minimum required syntax.

A.A. Usando uma instrução UPDATE simplesUsing a simple UPDATE statement

O exemplo a seguir atualiza uma única coluna de todas as linhas na tabela Person.Address.The following example updates a single column for all rows in the Person.Address table.

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

B.B. Atualizando várias colunasUpdating multiple columns

O exemplo a seguir atualiza os valores das colunas Bonus, CommissionPct e SalesQuota de todas as linhas da tabela SalesPerson.The following example updates the values in the Bonus, CommissionPct, and SalesQuota columns for all rows in the SalesPerson table.

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

Limitando as linhas que são atualizadasLimiting the Rows that Are Updated

Os exemplos desta seção demonstram as maneiras que podem ser usadas para limitar o número de linhas afetadas pela instrução UPDATE.Examples in this section demonstrate ways that you can use to limit the number of rows affected by the UPDATE statement.

C.C. Usando a cláusula WHEREUsing the WHERE clause

O exemplo a seguir usa a cláusula WHERE para especificar quais linhas devem ser atualizadas.The following example uses the WHERE clause to specify which rows to update. A instrução atualiza o valor da coluna Color da tabela Production.Product para todas as linhas que têm um valor existente de 'Red' na coluna Color e têm um valor na coluna Name que é iniciado por 'Road-250.'The statement updates the value in the Color column of the Production.Product table for all rows that have an existing value of 'Red' in the Color column and have a value in the Name column that starts with 'Road-250'.

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

D.D. Usando a cláusula TOPUsing the TOP clause

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.The following examples use the TOP clause to limit the number of rows that are modified in an UPDATE statement. Quando uma cláusula TOP (n) é usada com UPDATE, a operação de atualização é executada em uma seleção aleatória de um número 'n' de linhas.When a TOP (n) clause is used with UPDATE, the update operation is performed on a random selection of 'n' number of rows. O exemplo a seguir atualiza a coluna VacationHours em 25% para 10 linhas aleatórias na tabela Employee.The following example updates the VacationHours column by 25 percent for 10 random rows in the Employee table.

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

Caso seja necessário usar a cláusula TOP para aplicar atualizações em uma ordem cronológica significativa, será necessário usar TOP junto com ORDER BY em uma instrução de subseleção.If you must use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. O exemplo a seguir atualiza as horas de férias dos 10 funcionários com as datas de contratação mais antigas.The following example updates the vacation hours of the 10 employees with the earliest hire dates.

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

O exemplo a seguir atualiza o valor PerAssemnblyQty para todas as partes e componentes que são usados direta ou indiretamente para criar o ProductAssemblyID 800.The following example updates the PerAssemnblyQty value for all parts and components that are used directly or indirectly to create the ProductAssemblyID 800. A expressão de tabela comum retorna uma lista hierárquica de partes que são usadas diretamente para compilar ProductAssemblyID 800 e partes que são usadas para compilar esses componentes e assim por diante.The common table expression returns a hierarchical list of parts that are used directly to build ProductAssemblyID 800 and parts that are used to build those components, and so on. Somente as linhas retornadas pela expressão de tabela comum são modificadas.Only the rows returned by the common table expression are modified.

USE AdventureWorks2012;  
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.F. Usando a cláusula WHERE CURRENT OFUsing the WHERE CURRENT OF clause

O exemplo a seguir usa a cláusula WHERE CURRENT OF para atualizar apenas a linha na qual o cursor está posicionado.The following example uses the WHERE CURRENT OF clause to update only the row on which the cursor is positioned. Quando um cursor estiver baseado em uma junção, só o table_name especificado na instrução UPDATE é modificado.When a cursor is based on a join, only the table_name specified in the UPDATE statement is modified. Outras tabelas que participam do cursor não são afetadas.Other tables participating in the cursor are not affected.

USE AdventureWorks2012;  
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 colunaSetting Column Values

Os exemplos desta seção demonstram como atualizar colunas por meio de valores computados, subconsultas e valores DEFAULT.Examples in this section demonstrate updating columns by using computed values, subqueries, and DEFAULT values.

G.G. Especificando um valor computadoSpecifying a computed value

O exemplo a seguir usa valores computados em uma instrução UPDATE.The following examples uses computed values in an UPDATE statement. O exemplo dobra o valor na coluna ListPrice de todas as linhas da tabela Product.The example doubles the value in the ListPrice column for all rows in the Product table.

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

H.H. Especificando um operador compostoSpecifying a compound operator

O exemplo a seguir usa a variável @NewPrice para aumentar o preço de todas as bicicletas vermelhas obtendo o preço atual e adicionando 10.The following example uses the variable @NewPrice to increment the price of all red bicycles by taking the current price and adding 10 to it.

USE AdventureWorks2012;  
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 o ' - tool malfunction' de dados ao valor existente na coluna Name para linhas que têm um ScrapReasonID entre 10 e 12.The following example uses the compound operator += to append the data ' - tool malfunction' to the existing value in the column Name for rows that have a ScrapReasonID between 10 and 12.

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

I.I. Especificando uma subconsulta na cláusula SETSpecifying a subquery in the SET clause

O exemplo a seguir usa uma subconsulta na cláusula SET para determinar o valor que é usado para atualizar a coluna.The following example uses a subquery in the SET clause to determine the value that is used to update the column. A subconsulta deve retornar apenas um valor escalar (ou seja, um único valor por linha).The subquery must return only a scalar value (that is, a single value per row). O exemplo modifica a coluna SalesYTD na tabela SalesPerson para refletir as vendas mais recentes registradas na tabela SalesOrderHeader.The example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table. A subconsulta agrega as vendas de cada vendedor na instrução UPDATE.The subquery aggregates the sales for each salesperson in the UPDATE statement.

USE AdventureWorks2012;  
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.J. Atualizando linhas com valores DEFAULTUpdating rows using DEFAULT values

O exemplo a seguir define a coluna CostRate como seu valor padrão (0.00) para todas as linhas que têm um valor de CostRate maior que 20.00.The following example sets the CostRate column to its default value (0.00) for all rows that have a CostRate value greater than 20.00.

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

Especificando objetos de destino diferentes de tabelas padrãoSpecifying Target Objects Other Than Standard Tables

Os exemplos desta seção demonstram como atualizar linhas por meio da especificação de uma exibição, um alias de tabela ou uma variável de tabela.Examples in this section demonstrate how to update rows by specifying a view, table alias, or table variable.

K.K. Especificando uma exibição como objeto de destinoSpecifying a view as the target object

O exemplo a seguir atualiza linhas em uma tabela por meio da especificação de uma exibição como o objeto de destino.The following example updates rows in a table by specifying a view as the target object. A definição da exibição faz referência a várias tabelas, no entanto, a instrução UPDATE têm êxito porque faz referência a várias colunas de apenas uma das tabelas subjacentes.The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables. A instrução UPDATE falhará se as colunas das duas tabelas forem especificadas.The UPDATE statement would fail if columns from both tables were specified. Para obter mais informações, confira Modificar dados por meio de uma exibição.For more information, see Modify Data Through a View.

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

L.L. Especificando um alias de tabela como objeto de destinoSpecifying a table alias as the target object

O exemplo a seguir atualiza linhas da tabela Production.ScrapReason.The follow example updates rows in the table Production.ScrapReason. O alias de tabela atribuído a ScrapReason na cláusula FROM é especificado como o objeto de destino na cláusula UPDATE.The table alias assigned to ScrapReason in the FROM clause is specified as the target object in the UPDATE clause.

USE AdventureWorks2012;  
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.M. Especificando uma variável de tabela como objeto de destinoSpecifying a table variable as the target object

O exemplo a seguir atualiza linhas em uma variável de tabela.The following example updates rows in a table variable.

USE AdventureWorks2012;  
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 tabelasUpdating Data Based on Data From Other Tables

Os exemplos desta seção demonstram métodos para a atualização de linhas de uma tabela com base nas informações de outra tabela.Examples in this section demonstrate methods of updating rows from one table based on information in another table.

N.N. Usando uma instrução UPDATE com informações de outra tabelaUsing the UPDATE statement with information from another table

O exemplo a seguir modifica a coluna SalesYTD da tabela SalesPerson para refletir as vendas mais recentes registradas na tabela SalesOrderHeader.The following example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table.

USE AdventureWorks2012;  
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 presume que seja registrada apenas uma venda para um vendedor especificado em uma determinada data e que as atualizações sejam atuais.The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. Se puder ser registrada no mesmo dia mais de uma venda para um vendedor especificado, o exemplo mostrado não funcionará corretamente.If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. O exemplo foi executado sem erros, mas cada valor de SalesYTD foi atualizado com apenas uma venda, independentemente de quantas vendas realmente ocorreram naquele dia.The example runs without error, but each SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. Isso ocorre porque uma única instrução UPDATE nunca atualiza a mesma linha duas vezes.This is because a single UPDATE statement never updates the same row two times.

Em situações em que pode haver mais de uma venda no mesmo dia para um vendedor especificado, devem ser agregadas todas as vendas de cada vendedor na instrução UPDATE, como mostrado no seguinte exemplo:In the situation in which more than one sale for a specified salesperson can occur on the same day, all the sales for each sales person must be aggregated together within the UPDATE statement, as shown in the following example:

USE AdventureWorks2012;  
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 remotaUpdating Rows in a Remote Table

Os exemplos nesta seção demonstram como atualizar linhas em uma tabela de destino remota usando um servidor vinculado ou uma função de conjunto de linhas para referenciar a tabela remota.Examples in this section demonstrate how to update rows in a remote target table by using a linked server or a rowset function to reference the remote table.

O.O. Atualizando dados em uma tabela remota por meio de um servidor vinculadoUpdating data in a remote table by using a linked server

O exemplo a seguir atualiza uma tabela em um servidor remoto.The following example updates a table on a remote server. O exemplo começa criando um link com a fonte de dados remota usando sp_addlinkedserver.The example begins by creating a link to the remote data source by using sp_addlinkedserver. O nome de servidor vinculado, MyLinkServer, é especificado então como parte do nome de objeto de quatro partes no formulário server.catalog.schema.object.The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form server.catalog.schema.object. Observe que você deve especificar um nome de servidor válido para @datasrc.Note that you must specify a valid server name for @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'MyLinkServer',  
    @srvproduct = N' ',  
    @provider = N'SQLNCLI10',   
    @datasrc = N'<server name>',  
    @catalog = N'AdventureWorks2012';  
GO  
USE AdventureWorks2012;  
GO  
-- Specify the remote data source using a four-part name   
-- in the form linked_server.catalog.schema.object.  

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

P.P. Atualizando dados em uma tabela remota por meio da função OPENQUERYUpdating data in a remote table by using the OPENQUERY function

O exemplo a seguir atualiza uma linha em uma tabela remota especificando a função de conjunto de linhas OPENQUERY.The following example updates a row in a remote table by specifying the OPENQUERY rowset function. O nome de servidor vinculado criado no exemplo anterior é usado neste exemplo.The linked server name created in the previous example is used in this example.

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

Q.Q. Atualizando dados em uma tabela remota por meio da função OPENDATASOURCEUpdating data in a remote table by using the OPENDATASOURCE function

O exemplo a seguir insere uma linha em uma tabela remota especificando a função do conjunto de linhas OPENDATASOURCE.The following example inserts a row into a remote table by specifying the OPENDATASOURCE rowset function. Especifique um nome do servidor válido para a fonte de dados usando o formato server_name ou server_name\instance_name.Specify a valid server name for the data source by using the format server_name or server_name\instance_name. Pode ser necessário configurar a instância do SQL ServerSQL Server para Consultas Distribuídas Ad Hoc.You may need to configure the instance of SQL ServerSQL Server for Ad Hoc Distributed Queries. Para obter mais informações, confira Opção de configuração do servidor ad hoc distributed queries.For more information, see ad hoc distributed queries Server Configuration Option.

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

Atualizando tipos de dados de objeto grandeUpdating Large Object Data Types

Os exemplos desta seção demonstram métodos de atualização de valores em colunas que estão definidas com tipos de dados LOB (objetos grandes).Examples in this section demonstrate methods of updating values in columns that are defined with large object (LOB) data types.

R.R. Usando UPDATE com .WRITE para modificar dados em uma coluna nvarchar(max)Using UPDATE with .WRITE to modify data in an nvarchar(max) column

O exemplo a seguir usa a cláusula .WRITE para atualizar um valor parcial em DocumentSummary, uma coluna nvarchar(max) na tabela Production.Document.The following example uses the .WRITE clause to update a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table. A palavra components é substituída pela palavra features especificando-se a palavra de substituição, o local de início (deslocamento) da palavra a ser substituída nos dados existentes e o número de caracteres a serem substituídos (comprimento).The word components is replaced with the word features by specifying the replacement word, the starting location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). O exemplo também usa a cláusula OUTPUT para retornar as imagens anterior e posterior da coluna DocumentSummary para a variável de tabela @MyTableVar.The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.

USE AdventureWorks2012;  
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.S. Usando UPDATE com .WRITE para adicionar e remover dados em uma coluna nvarchar(max)Using UPDATE with .WRITE to add and remove data in an nvarchar(max) column

Os exemplos a seguir adicionam e removem dados de uma coluna nvarchar(max) que tem um valor definido como NULL no momento.The following examples add and remove data from an nvarchar(max) column that has a value currently set to NULL. Como a cláusula .WRITE não pode ser usada para modificar uma coluna NULL, a coluna será populada com os dados temporários inicialmente.Because the .WRITE clause cannot be used to modify a NULL column, the column is first populated with temporary data. Em seguida, esses dados são substituídos pelos dados corretos por meio da cláusula .WRITE.This data is then replaced with the correct data by using the .WRITE clause. Os exemplos adicionais acrescentam dados ao final do valor da coluna, removem (truncam) dados da coluna e, finalmente, removem dados parciais da coluna.The additional examples append data to the end of the column value, remove (truncate) data from the column and, finally, remove partial data from the column. As instruções SELECT exibem a modificação dos dados gerada em cada instrução UPDATE.The SELECT statements display the data modification generated by each UPDATE statement.

USE AdventureWorks2012;  
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.T. Usando UPDATE com OPENROWSET para modificar uma coluna varbinary(max)Using UPDATE with OPENROWSET to modify a varbinary(max) column

O exemplo a seguir substitui uma imagem existente armazenada em uma coluna varbinary(max) por uma nova imagem.The following example replaces an existing image stored in a varbinary(max) column with a new image. A função OPENROWSET é usada com a opção BULK para carregar a imagem na coluna.The OPENROWSET function is used with the BULK option to load the image into the column. Este exemplo presume que existe um arquivo denominado Tires.jpg no caminho de arquivo especificado.This example assumes that a file named Tires.jpg exists in the specified file path.

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

U.U. Usando UPDATE para modificar dados FILESTREAMUsing UPDATE to modify FILESTREAM data

O exemplo a seguir usa a instrução UPDATE para modificar os dados no arquivo do sistema de arquivos.The following example uses the UPDATE statement to modify the data in the file system file. Esse método não é recomendado para streaming de grandes quantidades de dados para um arquivo.We do not recommend this method for streaming large amounts of data to a file. Use as interfaces do Win32 adequadas.Use the appropriate Win32 interfaces. O exemplo a seguir substitui qualquer texto no registro do arquivo pelo texto Xray 1.The following example replaces any text in the file record with the text Xray 1. Para obter mais informações, veja FILESTREAM (SQL Server).For more information, see FILESTREAM (SQL Server).

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

Atualizando tipos definidos pelo usuárioUpdating User-defined Types

Os exemplos a seguir modificam valores em colunas de tipo de dados CLR UDT (definido pelo usuário).The following examples modify values in CLR user-defined type (UDT) columns. Três métodos são demonstrados.Three methods are demonstrated. Para obter mais informações sobre colunas definidas pelo usuário, confira Tipos CLR definidos pelo usuário.For more information about user-defined columns, see CLR User-Defined Types.

V.V. Usando um tipo de dados do sistemaUsing a system data type

É possível atualizar um UDT fornecendo um valor em um tipo de dados do sistema SQL ServerSQL Server, desde que o tipo definido pelo usuário ofereça suporte à conversão implícita ou explícita do referido tipo.You can update a UDT by supplying a value in a SQL ServerSQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. O exemplo a seguir mostra como atualizar um valor em uma coluna de tipo definido pelo usuário Point, convertendo-o explicitamente de uma cadeia de caracteres:The following example shows how to update a value in a column of user-defined type Point, by explicitly converting from a string.

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

W.W. Invocando um métodoInvoking a method

É possível atualizar um UDT com a invocação de um método, marcado como um modificador, do tipo definido pelo usuário, para executar a atualização.You can update a UDT by invoking a method, marked as a mutator, of the user-defined type, to perform the update. O exemplo a seguir invoca um método modificador de tipo Point denominado SetXY.The following example invokes a mutator method of type Point named SetXY. Isso atualiza o estado da instância do tipo.This updates the state of the instance of the type.

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

X.X. Modificando o valor de uma propriedade ou de um membro de dadosModifying the value of a property or data member

É possível atualizar um UDT com a modificação do valor de uma propriedade registrada ou membro de dados público do tipo definido pelo usuário.You can update a UDT by modifying the value of a registered property or public data member of the user-defined type. A expressão que fornece o valor deve poder ser implicitamente convertida para o tipo da propriedade.The expression supplying the value must be implicitly convertible to the type of the property. O exemplo a seguir modifica o valor de propriedade X do tipo definido pelo usuário Point:The following example modifies the value of property X of user-defined type Point.

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

Substituindo o comportamento padrão do otimizador de consulta usando dicasOverriding the Default Behavior of the Query Optimizer by Using Hints

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.Examples in this section demonstrate how to use table and query hints to temporarily override the default behavior of the query optimizer when processing the UPDATE statement.

Cuidado

Como o otimizador de consulta do SQL ServerSQL Server normalmente seleciona o melhor plano de execução para uma consulta, é recomendável que desenvolvedores e administradores de banco de dados experientes usem as dicas apenas como um último recurso.Because the SQL ServerSQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

Y.Y. Especificando uma dica de tabelaSpecifying a table hint

O exemplo a seguir especifica a dica de tabela TABLOCK.The following example specifies the table hint TABLOCK. Esta dica especifica que um bloqueio compartilhado é utilizado na tabela Production.Product e mantido até o término da instrução UPDATE.This hint specifies that a shared lock is taken on the table Production.Product and held until the end of the UPDATE statement.

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

Z.Z. Especificando uma dica de consultaSpecifying a query hint

O exemplo a seguir especifica a dica de consultaOPTIMIZE FOR (@variable) na instrução UPDATE.The following example specifies the query hintOPTIMIZE FOR (@variable) in the UPDATE statement. A dica instrui o otimizador de consultas a usar um valor específico para uma variável local quando a consulta é compilada e otimizada.This hint instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. O valor é usado somente durante a otimização da consulta e não durante sua execução.The value is used only during query optimization, and not during query execution.

USE AdventureWorks2012;  
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 UPDATECapturing the Results of the UPDATE Statement

Os 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.Examples in this section demonstrate how to use the OUTPUT Clause to return information from, or expressions based on, each row affected by an UPDATE statement. Esses resultados podem ser retornados ao aplicativo de processamento para uso em mensagens de confirmação, arquivamentos e outros requisitos similares de aplicativo.These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.

AA.AA. Usando UPDATE com a cláusula OUTPUTUsing UPDATE with the OUTPUT clause

O exemplo a seguir atualiza a coluna VacationHours na tabela Employee em 25 por cento das primeiras 10 linhas e também define o valor na coluna ModifiedDate como a data atual.The following example updates the column VacationHours in the Employee table by 25 percent for the first 10 rows and also sets the value in the column ModifiedDate to the current date. A cláusula OUTPUT retorna o valor de VacationHours existente antes da aplicação da instrução UPDATE na coluna deleted.VacationHours e o valor atualizado na coluna inserted.VacationHours para a variável de tabela @MyTableVar.The OUTPUT clause returns the value of VacationHours that exists before applying the UPDATE statement in the deleted.VacationHours column and the updated value in the inserted.VacationHours column to the @MyTableVar table variable.

Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Para obter mais exemplos de uso da cláusula OUTPUT, confira Cláusula OUTPUT (Transact-SQL).For more examples using the OUTPUT clause, see OUTPUT Clause (Transact-SQL).

USE AdventureWorks2012;  
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çõesUsing UPDATE in other statements

Os exemplos desta seção demonstram como usar o UPDATE em outras instruções.Examples in this section demonstrate how to use UPDATE in other statements.

AB.AB. Usando UPDATE em um procedimento armazenadoUsing UPDATE in a stored procedure

O seguinte exemplo usa uma instrução UPDATE em um procedimento armazenado:The following example uses an UPDATE statement in a stored procedure. o procedimento utiliza um parâmetro de entrada, @NewHours, e um parâmetro de saída, @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. O valor do parâmetro @NewHours é usado na instrução UPDATE para atualizar a coluna VacationHours na tabela HumanResources.Employee.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. O parâmetro de saída @RowCount é usado para retornar o número de linhas afetadas para uma variável local.The @RowCount output parameter is used to return the number of rows affected to a local variable. A expressão CASE é usada na cláusula SET para determinar condicionalmente o valor que é definido para VacationHours.The CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. Quando o funcionário é 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.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

USE AdventureWorks2012;  
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.AC. Usando UPDATE em um bloco TRY…CATCHUsing UPDATE in a TRY…CATCH Block

O exemplo a seguir usa uma instrução UPDATE em um bloco TRY...CATCH para tratar os erros de execução que podem ocorrer durante a operação de atualização.The following example uses an UPDATE statement in a TRY…CATCH block to handle execution errors that may occur during the update operation.

USE AdventureWorks2012;  
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: SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseExamples: SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

AD.AD. Usando uma instrução UPDATE simplesUsing a simple UPDATE statement

Os exemplos a seguir mostram como todas as linhas podem ser afetadas quando uma cláusula WHERE não é usada para especificar as linhas a serem atualizadas.The following examples show how all rows can be affected when a WHERE clause is not used to specify the row (or rows) to update.

Este exemplo atualiza os valores nas colunas EndDate e CurrentFlag para todas as linhas na tabela DimEmployee.This example updates the values in the EndDate and CurrentFlag columns for all rows in the DimEmployee table.

-- Uses AdventureWorks  

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

Também é possível usar valores computados em uma instrução UPDATE.You can also use computed values in an UPDATE statement. O exemplo a seguir dobra o valor na coluna ListPrice de todas as linhas da tabelaProduct.The following example doubles the value in the ListPrice column for all rows in the Product table.

-- Uses AdventureWorks  

UPDATE DimEmployee  
SET BaseRate = BaseRate * 2;  

AE.AE. Usando a instrução UPDATE com uma cláusula WHEREUsing the UPDATE statement with a WHERE clause

O exemplo a seguir usa a cláusula WHERE para especificar quais linhas devem ser atualizadas.The following example uses the WHERE clause to specify which rows to update.

-- Uses AdventureWorks  

UPDATE DimEmployee  
SET FirstName = 'Gail'  
WHERE EmployeeKey = 500;  

AF.AF. Usando a instrução UPDATE com rótuloUsing the UPDATE statement with label

O exemplo a seguir mostra o uso de um LABEL para a instrução UPDATE.The following example shows use of a LABEL for the UPDATE statement.

-- Uses AdventureWorks  

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

AG.AG. Usando uma instrução UPDATE com informações de outra tabelaUsing the UPDATE statement with information from another table

Este exemplo cria uma tabela para armazenar o total de vendas por ano.This example creates a table to store total sales by year. Ele atualiza o total de vendas do ano de 2004, executando uma instrução SELECT na tabela FactInternetSales.It updates the total sales for the year 2004 by running a SELECT statement against the FactInternetSales table.

-- 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.AH. Substituição de junção ANSI para instruções de atualizaçãoANSI join replacement for update statements

É possível que você tenha uma atualização complexa que una mais de duas tabelas usando a sintaxe de junção ANSI para executar UPDATE ou DELETE.You may find you have a complex update that joins more than two tables together using ANSI joining syntax to perform the UPDATE or DELETE.

Imagine que você precisasse atualizar esta tabela:Imagine you had to update this table:

CREATE TABLE [dbo].[AnnualCategorySales]
(   [EnglishProductCategoryName]    NVARCHAR(50)    NOT NULL
,   [CalendarYear]                  SMALLINT        NOT NULL
,   [TotalSalesAmount]              MONEY           NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;  

A consulta original seria semelhante a esta:The original query might have looked something like this:

UPDATE  acs
SET     [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM    [dbo].[AnnualCategorySales]     AS acs
JOIN    (
        SELECT  [EnglishProductCategoryName]
        ,       [CalendarYear]
        ,       SUM([SalesAmount])              AS [TotalSalesAmount]
        FROM    [dbo].[FactInternetSales]       AS s
        JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
        JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
        JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
        JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
        WHERE   [CalendarYear] = 2004
        GROUP BY
                [EnglishProductCategoryName]
        ,       [CalendarYear]
        ) AS fis
ON  [acs].[EnglishProductCategoryName]  = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear]                = [fis].[CalendarYear]
;  

Como o SQL Data WarehouseSQL Data Warehouse não é compatível com junções ANSI na cláusula FROM de uma instrução UPDATE, não é possível copiar este código sem alterá-lo um pouco.Since SQL Data WarehouseSQL Data Warehouse does not support ANSI joins in the FROM clause of an UPDATE statement, you cannot copy this code over without changing it slightly.

É possível usar uma combinação de um CTAS e uma junção implícita para substituir este código:You can use a combination of a CTAS and an implicit join to replace this code:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

Consulte TambémSee Also

CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
Cursores (Transact-SQL) Cursors (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
Funções de texto e imagem (Transact-SQL) Text and Image Functions (Transact-SQL)
WITH common_table_expression (Transact-SQL) WITH common_table_expression (Transact-SQL)
FILESTREAM (SQL Server)FILESTREAM (SQL Server)