sql的update语句功能非常强大.docx

语法UPDATE { table_name WITH ( table_hint_limited [ ...n ] ) | view_name | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | @variable = column = expression } [ ,...n ] { { [ FROM { table_source } [ ,...n ] ] [ WHERE search_condition ] } | [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } [ OPTION ( query_hint [ ,...n ] ) ] table_source ::= table_name [ [ AS ] table_alias ] [ WITH ( table_hint [ ,...n ] ) ] | view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | joined_table joined_table ::= table_source join_type table_source ON search_condition | table_source CROSS JOIN table_source | joined_table join_type ::= [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] [ join_hint ] JOIN table_hint_limited ::= { FASTFIRSTROW | HOLDLOCK | PAGLOCK | READCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } table_hint ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK } query_hint ::= { { HASH | ORDER } GROUP | { CONCAT | HASH | MERGE } UNION | {LOOP | MERGE | HASH } JOIN | FAST number_rows | FORCE ORDER | MAXDOP | ROBUST PLAN | KEEP PLAN } 参数table_name 需要更新的表的名称。如果该表不在当前服务器或数据库中，或不为当前用户所有，这个名称可用链接服务器、数据库和所有者名称来限定。WITH ( table_hint_limited [ ...n ] ) 指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。有关表提示的信息，请参见 FROM。view_name 要更新的视图的名称。通过 view_name 来引用的视图必须是可更新的。用 UPDATE 语句进行的修改，至多只能影响视图的FROM 子句所引用的基表中的一个。有关可更新视图的更多信息，请参见 CREATE VIEW。rowset_function_limited OPENQUERY 或 OPENROWSET 函数，视提供程序功能而定。有关提供程序所需功能的更多信息，请参见 OLE DB 提供程序的UPDATE 和 DELETE 语句要求。有关行集函数的更多信息，请参见 OPENQUERY 和 OPENROWSET。SET 指定要更新的列或变量名称的列表。column_name 含有要更改数据的列的名称。column_name 必须驻留于 UPDATE 子句中所指定的表或视图中。标识列不能进行