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

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL Server 2019SQL Server 2019 中更改表或视图中的现有数据。Changes existing data in a table or view in SQL Server 2019SQL Server 2019. 有关示例,请参阅示例For examples, see Examples.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  

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

参数Arguments

WITH <common_table_expression>WITH <common_table_expression>
指定在 UPDATE 语句作用域内定义的临时命名结果集或视图,也称为公用表表达式 (CTE)。Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement. CTE 结果集派生自简单查询并由 UPDATE 语句引用。The CTE result set is derived from a simple query and is referenced by UPDATE statement.

公用表表达式还可与 SELECT、INSERT、DELETE 和 CREATE VIEW 等语句一起使用。Common table expressions can also be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements. 有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression) [ PERCENT ]
指定更新的行数或行数百分比。Specifies the number or percent of rows that are updated. expression 可以是行数或行的百分比。expression can be either a number or a percent of the rows.

与 INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

在 INSERT、UPDATE 和 DELETE 语句中,需要使用括号分隔 TOP 中的 expression 。Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

table_alias table_alias
在表示要从中更新行的表或视图的 FROM 子句中指定的别名。The alias specified in the FROM clause representing the table or view from which the rows are to be updated.

server_name server_name
是表或视图所在服务器的名称(使用链接服务器名称或 OPENDATASOURCE 函数作为服务器名称)。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. 如果指定了 server_name,则需要 database_name 和 schema_name 。If server_name is specified, database_name and schema_name are required.

database_namedatabase_name
数据库的名称。Is the name of the database.

schema_nameschema_name
表或视图所属架构的名称。Is the name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
要更新行的表或视图的名称。Is the name of the table or view from which the rows are to be updated. table_or_view_name 引用的视图必须可更新,并且只在该视图的 FROM 子句中引用一个基表 。The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. 有关可更新视图的详细信息,请参阅 CREATE VIEW (Transact-SQL)For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limited rowset_function_limited
OPENQUERYOPENROWSET 函数,视提供程序的功能而定。Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities.

WITH ( <Table_Hint_Limited> ) WITH ( <Table_Hint_Limited> )
指定目标表允许的一个或多个表提示。Specifies one or more table hints that are allowed for a target table. 需要有 WITH 关键字和括号。The WITH keyword and the parentheses are required. 不允许 NOLOCK 和 READUNCOMMITTED。NOLOCK and READUNCOMMITTED are not allowed. 有关表提示的信息,请参阅表提示 (Transact-SQL)For information about table hints, see Table Hints (Transact-SQL).

@table_variable @table_variable
变量指定为表源。Specifies a table variable as a table source.

SETSET
指定要更新的列或变量名称的列表。Specifies the list of column or variable names to be updated.

column_name column_name
包含要更改的数据的列。Is a column that contains the data to be changed. column_name 必须存在于 table_or view_name 中 。column_name must exist in table_or view_name. 不能更新标识列。Identity columns cannot be updated.

expressionexpression
返回单个值的变量、文字值、表达式或嵌套 select 语句(加括号)。Is a variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value. expression 返回的值替换 column_name 或 @ 变量中的现有值 。The value returned by expression replaces the existing value in column_name or @variable.

备注

当引用 Unicode 字符数据类型 nchar、nvarchar 和 ntext 时,“expression”应采用大写字母“N”作为前缀 。When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'. 如果未指定“N”,则 SQL ServerSQL Server 会将字符串转换为与数据库或列的默认排序规则相对应的代码页。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. 此代码页中没有的字符都将丢失。Any characters not found in this code page are lost.

DEFAULTDEFAULT
指定用为列定义的默认值替换列中的现有值。Specifies that the default value defined for the column is to replace the existing value in the column. 如果该列没有默认值并且定义为允许 Null 值,则该参数也可用于将列更改为 NULL。This can also be used to change the column to NULL if the column has no default and is defined to allow null values.

{ += | -= | *= | /= | %= | &= | ^= | |= }{ += | -= | *= | /= | %= | &= | ^= | |= }
复合赋值运算符:Compound assignment operator:
+= 相加并赋值+= Add and assign
-= 相减并赋值-= Subtract and assign
*= 相乘并赋值*= Multiply and assign
/= 相除并赋值/= Divide and assign
%= 取模并赋值%= Modulo and assign
&= “位与”并赋值&= Bitwise AND and assign
^= “位异或”并赋值^= Bitwise XOR and assign
|= “位或”并赋值|= Bitwise OR and assign

udt_column_name udt_column_name
用户定义类型列。Is a user-defined type column.

property_name | field_name property_name | field_name
用户定义类型的公共属性或公共数据成员。Is a public property or public data member of a user-defined type.

method_name ( argument [ ,... n] ) method_name ( argument [ ,... n] )
带一个或多个参数的 udt_column_name 的非静态公共赋值函数方法 。Is a nonstatic public mutator method of udt_column_name that takes one or more arguments.

.WRITE (expression,@Offset,@Length) . WRITE (expression,@Offset,@Length)
指定要修改的 column_name 值的一部分。Specifies that a section of the value of column_name is to be modified. expression 替换从 column_name 的 @Offset 开始的 @Length 单位 。expression replaces @Length units starting from @Offset of column_name. 使用该子句只能指定 varchar(max)、nvarchar(max) 或 varbinary(max) 的列 。Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. column_name 不能为 NULL,也不能由表名或表别名限定 。column_name cannot be NULL and cannot be qualified with a table name or table alias.

expression 是复制到 column_name 的值 。expression is the value that is copied to column_name. expression 的计算结果必须为 column_name 类型或者 expression 必须能够隐式强制转换为此类型 。expression must evaluate to or be able to be implicitly cast to the column_name type. 如果 expression 设置为 NULL,则忽略 @Length,并将 column_name 中的值按指定的 @Offset 截断 。If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.

@Offset 是 column_name 值中的起点,从该点开始编写 expression 。@Offset is the starting point in the value of column_name at which expression is written. @Offset 是基于零的序号位置,数据类型为 bigint,不能为负数 。 @Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. 如果 @Offset 为 NULL,则更新操作将在现有 column_name 值的结尾追加 expression,并忽略 @Length 。If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. 如果 @Offset 大于 column_name 值的长度,则 数据库引擎Database Engine 将返回错误 。If @Offset is greater than the length of the column_name value, the 数据库引擎Database Engine returns an error. 如果 @Offset 加上 @Length 超出了列中基础值的限度,则将删除到值的最后一个字符 。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. 如果 @Offset 加上 LEN(expression) 大于声明的基础大小,则将出现错误 。If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.

@Length 是指列中某个部分的长度,从 @Offset 开始,该长度由 expression 替换 。@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length 是 bigint 并且不能为负数 。 @Length is bigint and cannot be a negative number. 如果 @Length 为 NULL,则更新操作将删除从 @Offset 到 column_name 值的结尾的所有数据 。If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

有关详细信息,请参阅“备注”。For more information, see Remarks.

@ variable @ variable
已声明的变量,该变量将设置为 expression 所返回的值 。Is a declared variable that is set to the value returned by expression.

SET @ variable = column = expression 将变量设置为与列相同的值。SET @variable = column = expression sets the variable to the same value as the column. 这与 SET @ variable = column, column = expression 不同,后者将变量设置为列更新前的值。This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

<OUTPUT_Clause><OUTPUT_Clause>
在 UPDATE 操作中,返回更新后的数据或基于更新后的数据的表达式。Returns updated data or expressions based on it as part of the UPDATE operation. 针对远程表或视图的任何 DML 语句都不支持 OUTPUT 子句。The OUTPUT clause is not supported in any DML statements that target remote tables or views. 有关详细信息,请参阅 OUTPUT 子句 (Transact-SQL)For more information, see OUTPUT Clause (Transact-SQL).

FROM <table_source>FROM <table_source>
指定将表、视图或派生表源用于为更新操作提供条件。Specifies that a table, view, or derived table source is used to provide the criteria for the update operation. 有关详细信息,请参阅 FROM (Transact-SQL)For more information, see FROM (Transact-SQL).

如果所更新对象与 FROM 子句中的对象相同,并且在 FROM 子句中对该对象只有一个引用,则指定或不指定对象别名均可。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. 如果更新的对象在 FROM 子句中出现了不止一次,则对该对象的一个(并且只有一个)引用不能指定表别名。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. FROM 子句中对该对象的所有其他引用都必须包含对象别名。All other references to the object in the FROM clause must include an object alias.

带 INSTEAD OF UPDATE 触发器的视图不能是含有 FROM 子句的 UPDATE 的目标。A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.

备注

FROM 子句中对 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何调用与对用作更新目标的这些函数的任何调用都是分开独立计算的,即使为两个调用提供的参数相同也是如此。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. 具体而言,应用到上述任一调用的结果的筛选器或联接条件不会影响其他调用的结果。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
指定条件来限定所更新的行。Specifies the conditions that limit the rows that are updated. 根据所使用的 WHERE 子句的形式,有两种更新形式:There are two forms of update based on which form of the WHERE clause is used:

  • 搜索更新指定搜索条件来限定要删除的行。Searched updates specify a search condition to qualify the rows to delete.

  • 定位更新使用 CURRENT OF 子句指定游标。Positioned updates use the CURRENT OF clause to specify a cursor. 更新操作发生在游标的当前位置。The update operation occurs at the current position of the cursor.

<search_condition><search_condition>
为要更新的行指定需满足的条件。Specifies the condition to be met for the rows to be updated. 搜索条件也可以是联接所基于的条件。The search condition can also be the condition upon which a join is based. 对搜索条件中可以包含的谓词数量没有限制。There is no limit to the number of predicates that can be included in a search condition. 有关谓词和搜索条件的详细信息,请参阅搜索条件 (Transact-SQL)For more information about predicates and search conditions, see Search Condition (Transact-SQL).

CURRENT OFCURRENT OF
指定更新在指定游标的当前位置进行。Specifies that the update is performed at the current position of the specified cursor.

使用 WHERE CURRENT OF 子句的定位更新将在游标的当前位置更新单行。A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. 这比使用 WHERE <search_condition> 子句限定所更新行的搜索更新更精确。This can be more accurate than a searched update that uses a WHERE <search_condition> clause to qualify the rows to be updated. 当搜索条件不唯一标识一行时,搜索更新将修改多行。A searched update modifies multiple rows when the search condition does not uniquely identify a single row.

GLOBALGLOBAL
指定 cursor_name 是指全局游标 。Specifies that cursor_name refers to a global cursor.

cursor_name cursor_name
要从中进行提取的开放游标的名称。Is the name of the open cursor from which the fetch should be made. 如果同时存在名为 cursor_name 的全局游标和局部游标,那么,在指定了 GLOBAL 时,该参数是指全局游标;否则是指局部游标 。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. 游标必须允许更新。The cursor must allow updates.

cursor_variable_name cursor_variable_name
cursor 变量的名称。Is the name of a cursor variable. cursor_variable_name 必须引用允许更新的游标。cursor_variable_name must reference a cursor that allows updates.

OPTION ( <query_hint> [ ,... n ] ) OPTION ( <query_hint> [ ,... n ] )
指定优化器提示用于自定义数据库引擎Database Engine处理语句的方式。Specifies that optimizer hints are used to customize the way the 数据库引擎Database Engine processes the statement. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

最佳实践Best Practices

使用 @@ROWCOUNT 函数返回插入到客户端应用程序的行数。Use the @@ROWCOUNT function to return the number of inserted rows to the client application. 有关详细信息,请参阅 @@ROWCOUNT (Transact-SQL)For more information, see @@ROWCOUNT (Transact-SQL).

可以在 UPDATE 语句中使用变量名称来显示受影响的旧值和新值,但仅当 UPDATE 语句影响单个记录时才应使用变量名称。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. 如果 UPDATE 语句影响多个记录,若要返回每个记录的旧值和新值,请使用 OUTPUT 子句If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause.

指定 FROM 子句为更新操作提供条件时务须小心。Use caution when specifying the FROM clause to provide the criteria for the update operation. 如果 UPDATE 语句包含了未指定每个更新列的位置只有一个可用值的 FROM 子句(换句话说,如果 UPDATE 语句是不确定性的),则其结果将不明确。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. 例如,对于下面脚本中的 UPDATE 语句,Table1 中的全部两行都满足 UPDATE 语句中 FROM 子句的限定条件;但是,将使用 Table1 中的哪一行来更新 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;  

当结合使用 FROM 和 WHERE CURRENT OF 子句时,可能发生同样的问题。The same problem can occur when the FROM and WHERE CURRENT OF clauses are combined. 在以下示例中,Table2 中的全部两行都满足 FROM 语句中 UPDATE 子句的限定条件。In the following example, both rows in Table2 meet the qualifications of the FROM clause in the UPDATE statement. 将使用 Table2 的哪一行来更新 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  

兼容性支持Compatibility Support

SQL ServerSQL Server 的未来版本中,将不再支持在 FROM 子句中使用应用于 UPDATE 或 DELETE 语句目标表的 READUNCOMMITTED 和 NOLOCK 提示。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. 请避免在新的开发工作上下文中使用这些提示,并计划修改当前使用它们的应用程序。Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

数据类型Data Types

所有的 char 和 nchar 列向右填充至定义长度 。All char and nchar columns are right-padded to the defined length.

如果 ANSI_PADDING 设置为 OFF,则会从插入 varchar 和 nvarchar 列的数据中删除所有尾随空格,但只包含空格的字符串除外 。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. 这些字符串被截断为空字符串。These strings are truncated to an empty string. 如果 ANSI_PADDING 设置为 ON,则插入尾随空格。If ANSI_PADDING is set to ON, trailing spaces are inserted. Microsoft SQL Server ODBC 驱动程序和用于 SQL Server 的 OLE DB 访问接口自动对每个连接设置 ANSI_PADDING ON。The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. 这可在 ODBC 数据源中进行配置,也可通过设置连接特性或属性进行配置。This can be configured in ODBC data sources or by setting connection attributes or properties. 有关详细信息,请参阅 SET ANSI_PADDING (Transact-SQL)For more information, see SET ANSI_PADDING (Transact-SQL).

更新 text、ntext 和 image 列Updating text, ntext, and image Columns

使用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化,向其列分配有效的文本指针,并且分配至少一个数据页(除非使用 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.

若要替换或修改大型 text、ntext 或 image 数据块,请使用 WRITETEXTUPDATETEXT,而不使用 UPDATE 语句 。To replace or modify large blocks of text, ntext, or image data, use WRITETEXT or UPDATETEXT instead of the UPDATE statement.

如果 UPDATE 语句在更新聚集键以及一个或者多个 text、ntext 或 image 列时可以更改多个行,则对这些列的部分更新将作为替换所有值来执行 。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.

重要

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中将删除 ntext、text 和 image 数据类型 。The ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新开发工作中使用这些数据类型,并考虑修改当前使用这些数据类型的应用程序。Avoid using these data types in new development work, and plan to modify applications that currently use them. 请改用 nvarchar(max)varchar(max)varbinary(max)Use nvarchar(max), varchar(max), and varbinary(max) instead.

更新大值数据类型Updating Large Value Data Types

使用 .WRITE (expression,@Offset,@Length) 子句执行 varchar(max)、nvarchar(max) 和 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. 例如,对 varchar(max) 列的部分更新可能只删除或修改该列的前 200 个字符,而完整更新则删除或修改该列中的所有数据 。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. 如果将数据库恢复模式设置为大容量日志模式或简单模式,则对插入或追加新数据的 .WRITE 更新进行最小日志记录 。.WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple. 在更新现有值时,不使用最小日志记录。Minimal logging is not used when existing values are updated. 有关详细信息,请参阅 事务日志 (SQL Server)For more information, see The Transaction Log (SQL Server).

当 UPDATE 语句导致下列任一操作时,数据库引擎Database Engine便会将部分更新转换为完整更新:The 数据库引擎Database Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:

  • 更改分区视图或表的键列。Changes a key column of the partitioned view or table.
  • 修改多行并且还将非唯一的聚集索引的键更新为非常量值。Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value.

不能使用 .WRITE 子句更新 NULL 列或将 column_name 的值设置为 NULL 。You cannot use the .WRITE clause to update a NULL column or set the value of column_name to NULL.

对于 varbinary 和 varchar 数据类型,以字节为单位指定 @Offset 和 @Length;对于 nvarchar 数据类型,则以字符为单位进行指定 。@Offset and @Length are specified in bytes for varbinary and varchar data types and in characters for the nvarchar data type. 已针对双字节字符集 (DBCS) 排序规则计算了适当的偏移量。The appropriate offsets are computed for double-byte character set (DBCS) collations.

为了获得最佳性能,建议按照块区大小为 8040 字节倍数的方式插入或更新数据。For best performance, we recommend that data be inserted or updated in chunk sizes that are multiples of 8040 bytes.

如果在 OUTPUT 子句中引用了由 .WRITE 子句修改的列,则该列的完整值(deleted.column_name 中的前像或 inserted.column_name 中的后像)返回到表变量中的指定列 。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. 请参阅后面的示例 R。See example R that follows.

若要针对其他字符或二进制数据类型获得相同的 .WRITE 功能,请使用 STUFF (Transact-SQL)To achieve the same functionality of .WRITE with other character or binary data types, use the STUFF (Transact-SQL).

更新用户定义类型列Updating User-defined Type Columns

更新用户定义类型列中的值可以通过下列方式之一完成:Updating values in user-defined type columns can be accomplished in one of the following ways:

  • 提供 SQL ServerSQL Server 系统数据类型的值,条件是该用户定义类型支持该类型的隐式转换或显式转换。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. 以下示例显示如何通过从字符串显式转换来更新用户定义类型 Point 的列中的值。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';  
    
  • 调用标记为“赋值函数”的用户定义类型的方法执行更新。Invoking a method, marked as a mutator, of the user-defined type, to perform the update. 以下示例调用类型 Point 的名为 SetXY 的赋值函数方法。The following example invokes a mutator method of type Point named SetXY. 这将更新该类型的实例状态。This updates the state of the instance of the type.

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

    备注

    如果对 SQL ServerSQL Server Null 值调用赋值函数方法,或者赋值函数方法产生的新值为 Null,则 Transact-SQLTransact-SQL 将返回错误。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.

  • 修改用户定义类型的已注册属性或公共数据成员的值。Modifying the value of a registered property or public data member of the user-defined type. 提供值的表达式必须可隐式转换为属性的类型。The expression supplying the value must be implicitly convertible to the type of the property. 以下示例修改用户定义类型 X 的属性 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';  
    

    若要修改同一用户定义类型列的不同属性,请发出多个 UPDATE 语句,或者调用该类型的赋值函数方法。To modify different properties of the same user-defined type column, issue multiple UPDATE statements, or invoke a mutator method of the type.

更新 FILESTREAM 数据Updating FILESTREAM Data

您可以使用 UPDATE 语句将 FILESTREAM 字段更新为 null 值、空值或相对较小的内联数据量。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. 但是,使用 Win32 接口可以更有效地将大量数据以流的方式导入到文件中。However, a large amount of data is more efficiently streamed into a file by using Win32 interfaces. 更新 FILESTREAM 字段时,即会修改文件系统中的基础 BLOB 数据。When you update a FILESTREAM field, you modify the underlying BLOB data in the file system. 将 FILESTREAM 字段设置为 NULL 即会删除与该字段相关联的 BLOB 数据。When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted. 不能使用 .WRITE(), 执行对 FILESTREAM 数据的部分更新。You cannot use .WRITE(), to perform partial updates to FILESTREAM data. 有关详细信息,请参阅 FILESTREAM (SQL Server)For more information, see FILESTREAM (SQL Server).

错误处理Error Handling

如果对行的更新违反了某个约束或规则,或违反了对列的 NULL 设置,或者新值是不兼容的数据类型,则取消该语句、返回错误并且不更新任何记录。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.

当 UPDATE 语句在表达式求值过程中遇到算术错误(溢出、被零除或域错误)时,则不进行更新。When an UPDATE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) during expression evaluation, the update is not performed. 批处理的剩余部分不再执行,并且返回错误消息。The rest of the batch is not executed, and an error message is returned.

如果对参与聚集索引的一列或多列的更新导致聚集索引和行的大小超过 8,060 字节,则更新失败并且返回错误消息。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.

互操作性Interoperability

仅当所修改的表是表变量时,才允许在用户定义函数的主体中使用 UPDATE 语句。UPDATE statements are allowed in the body of user-defined functions only if the table being modified is a table variable.

当对表的 UPDATE 操作定义 INSTEAD OF 触发器时,将运行触发器而不运行 UPDATE 语句。When an INSTEAD OF trigger is defined on UPDATE actions against a table, the trigger is running instead of the UPDATE statement. 早期版本的 SQL ServerSQL Server 只支持对 UPDATE 和其他数据修改语句定义 AFTER 触发器。Earlier versions of SQL ServerSQL Server only support AFTER triggers defined on UPDATE and other data modification statements. 不能在直接或间接引用定义有 INSTEAD OF 触发器的视图的 UPDATE 语句中指定 FROM 子句。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. 有关 INSTEAD OF 触发器的详细信息,请参阅 CREATE TRIGGER (Transact-SQL)For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

限制和局限Limitations and Restrictions

不能在直接或间接引用定义有 INSTEAD OF 触发器的视图的 UPDATE 语句中指定 FROM 子句。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. 有关 INSTEAD OF 触发器的详细信息,请参阅 CREATE TRIGGER (Transact-SQL)For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

在公用表表达式 (CTE) 是 UPDATE 语句的目标时,在该语句中对 CTE 的所有引用都必须匹配。When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. 例如,如果在 FROM 子句中向 CTE 分配了一个别名,则该别名必须用于对 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. 需要明确的 CTE 引用,因为 CTE 没有对象 ID,而 SQL ServerSQL Server 使用对象 ID 来识别对象与其别名之间的隐式关系。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. 如果没有这一关系,查询计划可能会产生意外的联接行为和意外的查询结果。Without this relationship, the query plan may produce unexpected join behavior and unintended query results. 以下示例演示在 CTE 是更新操作的目标对象时指定 CTE 的正确和不正确的方法。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  

下面是结果集:Here is the result set.

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

使用不正确匹配的 CTE 引用的 UPDATE 语句。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  

下面是结果集:Here is the result set.

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

锁定行为Locking Behavior

UPDATE 语句总是在其修改的表上获取排他 (X) 锁并在事务完成之前持有该锁。An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. 有了排他锁,其他事务都不可以修改数据。With an exclusive lock, no other transactions can modify data. 您可以指定表提示,以便通过指定其他锁定方法来覆盖 UPDATE 语句的持续时间的这一默认行为,但只建议经验丰富的开发人员和数据库管理员将提示用作最后的手段来执行。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. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

日志记录行为Logging Behavior

UPDATE 语句将记入日志;但是,对使用 .WRITE 子句对较大值数据类型的部分更新进行最小日志记录 。The UPDATE statement is logged; however, partial updates to large value data types using the . WRITE clause are minimally logged. 有关详细信息,请参阅上一节“数据类型”中的“更新大值数据类型”。For more information, see "Updating Large Value Data Types" in the earlier section "Data Types".

SecuritySecurity

权限Permissions

要求对目标表具有 UPDATE 权限。UPDATE permissions are required on the target table. 如果 UPDATE 语句包含 WHERE 子句,或 SET 子句中的 expression 使用了表中的某个列,则还要求要更新的表具有 SELECT 权限 。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.

UPDATE 权限默认授予 sysadmin 固定服务器角色的成员、db_owner 和 db_datawriter 固定数据库角色的成员以及表的所有者 。UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. sysadmin、db_owner 和 db_securityadmin 角色的成员和表所有者可以将权限传输给其他用户 。Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.

示例Examples

类别Category 作为特征的语法元素Featured syntax elements
基本语法Basic Syntax UPDATEUPDATE
限制更新的行Limiting the Rows that Are Updated WHERE • TOP • WITH 公用表表达式 • WHERE CURRENT OFWHERE • TOP • WITH common table expression • WHERE CURRENT OF
设置列值Setting Column Values 计算值 • 复合运算符 • 默认值 • 子查询computed values • compound operators • default values • subqueries
指定目标对象,而非标准表Specifying Target Objects Other than Standard Tables 视图 • 表变量 • 表别名views • table variables • table aliases
基于其他表中的数据更新数据Updating Data Based on Data From Other Tables FROMFROM
更新远程表中的行Updating Rows in a Remote Table 链接服务器 • OPENQUERY • OPENDATASOURCElinked server • OPENQUERY • OPENDATASOURCE
更新大型对象数据类型Updating Large Object Data Types .WRITE • OPENROWSET.WRITE • OPENROWSET
更新用户定义类型Updating User-defined Types 用户定义类型user-defined types
通过使用提示覆盖查询优化器的默认行为Overriding the Default Behavior of the Query Optimizer by Using Hints 表提示 • 查询提示table hints • query hints
捕获 UPDATE 语句的结果Capturing the Results of the UPDATE Statement OUTPUT 子句OUTPUT clause
在其他语句中使用 UPDATEUsing UPDATE in Other Statements 存储过程 • TRY…CATCHStored Procedures • TRY...CATCH

基本语法Basic Syntax

本节中的示例说明了使用最低要求的语法的 UPDATE 语句的基本功能。Examples in this section demonstrate the basic functionality of the UPDATE statement using the minimum required syntax.

A.A. 使用简单 UPDATE 语句Using a simple UPDATE statement

以下示例对于 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. 更新多个列Updating multiple columns

以下示例对于 Bonus 表中的所有行更新 CommissionPctSalesQuotaSalesPerson 列中的值。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  

限制更新的行Limiting the Rows that Are Updated

本节中的示例说明了可用于限制 UPDATE 语句所影响的行数的方法。Examples in this section demonstrate ways that you can use to limit the number of rows affected by the UPDATE statement.

C.C. 使用 WHERE 子句Using the WHERE clause

以下示例使用 WHERE 子句指定要更新的行。The following example uses the WHERE clause to specify which rows to update. 该语句对于 Color 列中已具有值“Red”且在以“Road-250”开头的 Production.Product 列中具有值的所有行更新 Color 表中 Name 列的值。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. 使用 TOP 子句Using the TOP clause

以下示例使用 TOP 子句来限制 UPDATE 语句中修改的行数。The following examples use the TOP clause to limit the number of rows that are modified in an UPDATE statement. 当 TOP (n) 子句与 UPDATE 一起使用时,将针对随机选择的“n”行执行更新操作 。When a TOP (n) clause is used with UPDATE, the update operation is performed on a random selection of 'n' number of rows. 以下示例按照 VacationHours 表中 10 个随机行的 25% 更新 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  

如果必须使用 TOP 来应用按有意义的时间顺序排列的更新,则必须在嵌套 select 语句中同时使用 TOP 和 ORDER BY。If you must use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. 下列示例更新了雇佣最早的 10 名雇员的假期小时数。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. 使用 WITH common_table_expression 子句Using the WITH common_table_expression clause

以下示例为直接或间接用于创建 PerAssemblyQty 的所有部件和组件更新 ProductAssemblyID 800 值。The following example updates the PerAssemblyQty value for all parts and components that are used directly or indirectly to create the ProductAssemblyID 800. 公用表表达式将返回用于直接生成 ProductAssemblyID 800 的部件和用于生成这些组件的部件等的层次结构列表。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. 只修改公用表表达式所返回的行。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. 使用 WHERE CURRENT OF 子句Using the WHERE CURRENT OF clause

以下示例使用 WHERE CURRENT OF 子句来只更新游标位于其上的行。The following example uses the WHERE CURRENT OF clause to update only the row on which the cursor is positioned. 如果游标基于某个联接,则只修改 UPDATE 语句中指定的 table_nameWhen a cursor is based on a join, only the table_name specified in the UPDATE statement is modified. 其他参与该游标的表不会受到影响。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  

设置列值Setting Column Values

本节中的示例说明了如何使用计算值、子查询和 DEFAULT 值来更新列。Examples in this section demonstrate updating columns by using computed values, subqueries, and DEFAULT values.

G.G. 指定计算值Specifying a computed value

以下示例在 UPDATE 语句中使用计算值。The following examples uses computed values in an UPDATE statement. 该示例将 Product 表中所有行的 ListPrice 列的值加倍。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. 指定复合运算符Specifying a compound operator

下面的示例使用变量 @NewPrice 通过在当前价格基础上加 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  

以下示例使用复合运算符 += 针对 ' - tool malfunction' 为 10 到 12 的行将数据 Name 追加到列 ScrapReasonID 中的现有值之后。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. 在 SET 子句中指定子查询Specifying a subquery in the SET clause

以下示例使用 SET 子句中的子查询来确定用于更新列的值。The following example uses a subquery in the SET clause to determine the value that is used to update the column. 子查询必须只返回标量值(即每行返回一个值)。The subquery must return only a scalar value (that is, a single value per row). 此示例修改 SalesYTD 表中的 SalesPerson 列,以反映 SalesOrderHeader 表中记录的最近销售情况。The example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table. 该子查询在 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. 使用 DEFAULT 值更新行Updating rows using DEFAULT values

以下示例针对 CostRate 值大于 CostRate 的所有行将 20.00 列设置为其默认值 (0.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;  

指定目标对象,而非标准表Specifying Target Objects Other Than Standard Tables

本节中的示例说明了如何通过指定视图、表别名或表变量来更新行。Examples in this section demonstrate how to update rows by specifying a view, table alias, or table variable.

K.K. 将视图指定为目标对象Specifying a view as the target object

以下示例通过将视图指定为目标对象来更新表中的行。The following example updates rows in a table by specifying a view as the target object. 该视图定义引用了多个表,但是 UPDATE 语句成功运行,因为它只引用了其中一个基础表中的列。The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables. 如果指定两个表中的列,UPDATE 语句将失败。The UPDATE statement would fail if columns from both tables were specified. 有关详细信息,请参阅通过视图修改数据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. 将表别名指定为目标对象Specifying a table alias as the target object

以下示例将更新 Production.ScrapReason 表中的行。The follow example updates rows in the table Production.ScrapReason. 将分配给 FROM 子句中 ScrapReason 的表别名指定为 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. 将表变量指定为目标对象Specifying a table variable as the target object

以下示例将更新表变量中的行。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  

基于其他表中的数据更新数据Updating Data Based on Data From Other Tables

本节中的示例说明了基于一个表中的信息更新另一个表中的行的方法。Examples in this section demonstrate methods of updating rows from one table based on information in another table.

N.N. 将 UPDATE 语句用于来自其他表的信息Using the UPDATE statement with information from another table

下面的示例修改 SalesYTD 表中的 SalesPerson 列,以反映 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  

上一个示例假定在特定日期只记录指定销售人员的一笔销售业务,并假定更新信息是最新的。The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. 如果在同一天中可以记录指定销售人员的多笔销售业务,所示的示例将不能正常运行。If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. 该示例运行时没有错误,但是每个 SalesYTD 值只能用一笔销售数据更新,而不管那一天实际发生了多少笔销售业务。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. 这是因为一条 UPDATE 语句永远不会对同一行更新两次。This is because a single UPDATE statement never updates the same row two times.

对于特定销售人员在同一天可销售不止一批的情况,每个销售人员的所有销售量必须在 UPDATE 语句中合计在一起,如下例所示: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  

更新远程表中的行Updating Rows in a Remote Table

本节中的示例说明了如何使用链接服务器行集函数引用一个远程目标表,从而更新该表中的行。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. 使用链接服务器更新远程表中的数据Updating data in a remote table by using a linked server

以下示例更新远程服务器上的表。The following example updates a table on a remote server. 该示例从使用 sp_addlinkedserver 创建指向远程数据源的链接开始。The example begins by creating a link to the remote data source by using sp_addlinkedserver. 然后,将链接服务器名称 MyLinkedServer 指定为 server.catalog.schema.object 形式的由四个部分组成的对象名称的一部分。The linked server name, MyLinkedServer, is then specified as part of the four-part object name in the form server.catalog.schema.object. 请注意,您必须为 @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'MyLinkedServer',  
    @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 MyLinkedServer.AdventureWorks2012.HumanResources.Department  
SET GroupName = N'Public Relations'  
WHERE DepartmentID = 4;  

P.P. 使用 OPENQUERY 函数更新远程表中的数据Updating data in a remote table by using the OPENQUERY function

以下示例通过指定 OPENQUERY 行集函数来更新远程表中的行。The following example updates a row in a remote table by specifying the OPENQUERY rowset function. 在之前例子中创建的链接服务器名称用于此示例。The linked server name created in the previous example is used in this example.

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

Q.Q. 使用 OPENDATASOURCE 函数更新远程表中的数据Updating data in a remote table by using the OPENDATASOURCE function

以下示例通过指定 OPENDATASOURCE 行集函数来更新远程表中的行。The following example updates a row in a remote table by specifying the OPENDATASOURCE rowset function. 通过使用 server_name 或 server_name\instance_name 格式,为该数据源指定一个有效的服务器名称 。Specify a valid server name for the data source by using the format server_name or server_name\instance_name. 您可能需要为即席分布式查询配置 SQL ServerSQL Server 实例。You may need to configure the instance of SQL ServerSQL Server for Ad Hoc Distributed Queries. 有关详细信息,请参阅即席分布式查询服务器配置选项For more information, see ad hoc distributed queries Server Configuration Option.

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

更新大型对象数据类型Updating Large Object Data Types

本节中的示例说明了如何更新使用大型对象 (LOB) 数据类型定义的列中的值。Examples in this section demonstrate methods of updating values in columns that are defined with large object (LOB) data types.

R.R. 使用包含 .WRITE 的 UPDATE 来修改 nvarchar(max) 列中的数据Using UPDATE with .WRITE to modify data in an nvarchar(max) column

以下示例使用 .WRITE 子句更新 DocumentSummaryProduction.Document 表内的 nvarchar(max) 列)中的部分值 。The following example uses the .WRITE clause to update a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table. 通过指定替换单词、现有数据中要替换的单词的开始位置(偏移量)以及要替换的字符数(长度),将单词 components 替换为单词 featuresThe 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). 此示例还使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回给 @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. 使用包含 .WRITE 的 UPDATE 在 nvarchar(max) 列中添加和删除数据Using UPDATE with .WRITE to add and remove data in an nvarchar(max) column

以下示例从当前值设置为 NULL 的 nvarchar(max) 列中添加和删除列 。The following examples add and remove data from an nvarchar(max) column that has a value currently set to NULL. 由于不能使用 .WRITE 子句修改 NULL 列,因此先使用临时数据填充该列。Because the .WRITE clause cannot be used to modify a NULL column, the column is first populated with temporary data. 然后,使用 .WRITE 子句将该数据替换为正确的数据。This data is then replaced with the correct data by using the .WRITE clause. 其他示例将数据追加到列值的结尾,从列中删除(截断)数据,最后从列中删除部分数据。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. SELECT 语句显示由每个 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. 使用包含 OPENROWSET 的 UPDATE 修改 varbinary(max) 列Using UPDATE with OPENROWSET to modify a varbinary(max) column

以下示例将 varbinary(max) 列中存储的现有图像替换为新图像 。The following example replaces an existing image stored in a varbinary(max) column with a new image. OPENROWSET 函数和 BULK 选项一起使用以将图像加载到列中。The OPENROWSET function is used with the BULK option to load the image into the column. 此示例假定指定的文件路径中存在名为 Tires.jpg 的文件。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. 使用 UPDATE 来修改 FILESTREAM 数据Using UPDATE to modify FILESTREAM data

以下示例使用 UPDATE 语句来修改文件系统文件中的数据。The following example uses the UPDATE statement to modify the data in the file system file. 我们不建议使用此方法将大量数据以流方式传输到文件。We do not recommend this method for streaming large amounts of data to a file. 请使用适当的 Win32 接口。Use the appropriate Win32 interfaces. 下面的示例将文件记录中的所有文本替换为文本 Xray 1The following example replaces any text in the file record with the text Xray 1. 有关详细信息,请参阅 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;  

更新用户定义类型Updating User-defined Types

以下示例修改 CLR 用户定义类型 (UDT) 列中的值。The following examples modify values in CLR user-defined type (UDT) columns. 演示了三种方法。Three methods are demonstrated. 有关用户定义的列的详细信息,请参阅 CLR 用户定义类型For more information about user-defined columns, see CLR User-Defined Types.

V.V. 使用系统数据类型Using a system data type

通过提供 SQL ServerSQL Server 系统数据类型的值可以更新 UDT,条件是该用户定义类型支持该类型的隐式转换或显式转换。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. 以下示例显示如何通过从字符串显式转换来更新用户定义类型 Point 的列中的值。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. 调用方法Invoking a method

通过调用标记为“赋值函数”的用户定义类型的方法执行更新,可以更新 UDT。You can update a UDT by invoking a method, marked as a mutator, of the user-defined type, to perform the update. 以下示例调用类型 Point 的名为 SetXY 的赋值函数方法。The following example invokes a mutator method of type Point named SetXY. 这将更新该类型的实例状态。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. 修改属性或数据成员的值Modifying the value of a property or data member

通过修改用户定义类型的已注册属性或公共数据成员的值,可以更新 UDT。You can update a UDT by modifying the value of a registered property or public data member of the user-defined type. 提供值的表达式必须可隐式转换为属性的类型。The expression supplying the value must be implicitly convertible to the type of the property. 以下示例修改用户定义类型 X 的属性 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';  

通过使用提示覆盖查询优化器的默认行为Overriding the Default Behavior of the Query Optimizer by Using Hints

本节中的示例说明了如何使用表提示和查询提示在处理 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.

注意

由于 SQL ServerSQL Server 查询优化器通常会为查询选择最佳执行计划,因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。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. 指定表提示Specifying a table hint

以下示例指定 表提示 TABLOCK。The following example specifies the table hint TABLOCK. 此提示指定对 Production.Product 表采用共享锁,并保持到 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. 指定查询提示Specifying a query hint

以下示例在 UPDATE 语句中指定查询提示OPTIMIZE FOR (@variable)The following example specifies the query hintOPTIMIZE FOR (@variable) in the UPDATE statement. 此提示指示查询优化器在编译和优化查询时对局部变量使用特定值。This hint instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. 仅在查询优化期间使用该值,在查询执行期间不使用该值。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-%';  

捕获 UPDATE 语句的结果Capturing the Results of the UPDATE Statement

本节中的示例说明如何使用 OUTPUT 子句从 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. 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。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. 使用包含 OUTPUT 子句的 UPDATEUsing UPDATE with the OUTPUT clause

以下示例针对前 10 行的 25% 更新 VacationHours 表中的列 Employee 并将 ModifiedDate 列中的值设置为当前日期。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. OUTPUT 子句将返回 VacationHours 的值,该值在将 UPDATE 列中的 deleted.VacationHours 语句和 inserted.VacationHours 列中的已更新值应用于 @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.

在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. 有关使用 OUTPUT 子句的更多示例,请参阅 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  

在其他语句中使用 UPDATEUsing UPDATE in other statements

本节中的示例说明了如何在其他语句中使用 UPDATE。Examples in this section demonstrate how to use UPDATE in other statements.

AB.AB. 在存储过程中使用 UPDATEUsing UPDATE in a stored procedure

下面的示例在一个存储过程中使用了 UPDATE 语句。The following example uses an UPDATE statement in a stored procedure. 该过程采用一个输入参数 @NewHours 和一个输出参数 @RowCountThe procedure takes one input parameter, @NewHours and one output parameter @RowCount. 在 UPDATE 语句中使用 @NewHours 参数值来更新表 HumanResources.Employee 中的列 VacationHoursThe @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. @RowCount 输出参数用于将影响的行数返回给一个局部变量。The @RowCount output parameter is used to return the number of rows affected to a local variable. 在 SET 子句中使用 CASE 表达式,以便确定按条件确定为 VacationHours 设置的值。The CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. 在按每小时向员工付薪时 (SalariedFlag = 0),VacationHours 设置为当前小时数加上 @NewHours 中指定的值;否则,VacationHours 设置为在 @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. 在 TRY…CATCH 块中使用 UPDATEUsing UPDATE in a TRY...CATCH Block

以下示例在 TRY…CATCH 块中使用 UPDATE 语句来处理在执行更新操作期间可能发生的执行错误。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  

示例:SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

AD.AD. 使用简单 UPDATE 语句Using a simple UPDATE statement

以下示例显示在未使用 WHERE 子句指定要更新的行时如何影响所有行。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.

此示例针对 DimEmployee 表中的所有行更新 EndDateCurrentFlag 列中的值。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';  

也可以在 UPDATE 语句中使用计算值。You can also use computed values in an UPDATE statement. 下面的示例将 ListPrice 表中所有行的 Product 列的值加倍。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. 将 UPDATE 语句用于 WHERE 子句Using the UPDATE statement with a WHERE clause

以下示例使用 WHERE 子句指定要更新的行。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. 将 UPDATE 语句用于标签Using the UPDATE statement with label

以下示例演示 UPDATE 语句中 LABEL 的用法。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. 将 UPDATE 语句用于来自其他表的信息Using the UPDATE statement with information from another table

此示例创建一个表,用于存储每年的总销售额。This example creates a table to store total sales by year. 通过对 FactInternetSales 表运行 SELECT 语句来更新 2004 年的总销售额。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. 更新语句的 ANSI 联接替换ANSI join replacement for update statements

可能会发现更新很复杂,它使用 ANSI 联接语法将两个以上的表联接在一起,以执行 UPDATE 或 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 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
)
;  

原始查询看起来可能如下: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]
;  

由于 SQL 数据仓库SQL Data Warehouse 不支持 UPDATE 语句中 FROM 子句的 ANSI 联接,因此需要稍作更改才能复制此代码。Since SQL 数据仓库SQL 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.

可以将 CTAS 和隐式联接结合使用来替换此代码: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
;

另请参阅See Also

CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
游标 (Transact-SQL) Cursors (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
文本与图像函数 (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)