sql的update语句功能非常强大.docx
《sql的update语句功能非常强大.docx》由会员分享，可在线阅读，更多相关《sql的update语句功能非常强大.docx（12页珍藏版）》请在冰豆网上搜索。
sql的update语句功能非常强大
语法
UPDATE
{
table_nameWITH（<
table_hint_limited>[...n]）
|view_name
|rowset_function_limited
}
SET
{column_name={expression|
DEFAULT|NULL}
|@variable=expression
|@variable=column=expression
}[,...n]
{{[FROM{}[
...n]]
[WHERE
]}
|
[WHERECURRENTOF
{{[GLOBAL]cursor_name}|
cursor_variable_name}
]}
[OPTION（[
...n]）]
:
:
=
table_name[[AS]table_alias][
WITH（[,...n]）]
|view_name[[AS]table_alias]
|rowset_function[[AS]table_alias
]
|derived_table[AS]table_alias[
（column_alias[,...n]）]
|
:
:
=
<
table_source>ON
|CROSSJOIN<
table_source>
|
:
:
=
[INNER|{{LEFT|RIGHT|FULL
}[OUTER]}]
[]
JOIN
:
:
=
{FASTFIRSTROW
|HOLDLOCK
|PAGLOCK
|READCOMMITTED
|REPEATABLEREAD
|ROWLOCK
|SERIALIZABLE
|TABLOCK
|TABLOCKX
|UPDLOCK
}
:
:
=
{INDEX（index_val[,...n]）
|FASTFIRSTROW
|HOLDLOCK
|NOLOCK
|PAGLOCK
|READCOMMITTED
|READPAST
|READUNCOMMITTED
|REPEATABLEREAD
|ROWLOCK
|SERIALIZABLE
|TABLOCK
|TABLOCKX
|UPDLOCK
}
:
:
=
{{HASH|ORDER}GROUP
|{CONCAT|HASH|MERGE}
UNION
|{LOOP|MERGE|HASH}JOIN
|FASTnumber_rows
|FORCEORDER
|MAXDOP
|ROBUSTPLAN
|KEEPPLAN
}
参数
table_name
需要更新的表的名称。
如果该表不在当前服务器或数据库中，或不为
当前用户所有，这个名称可用链接服务器、数据库和所有者名称来限
定。
WITH（[...n]）
指定目标表所允许的一个或多个表提示。
需要有WITH关键字和
圆括号。
不允许有READPAST、NOLOCK和READUNCOMMITTED。
有
关表提示的信息，请参见FROM。
view_name
要更新的视图的名称。
通过view_name来引用的视图必须是可
更新的。
用UPDATE语句进行的修改，至多只能影响视图的
FROM子句所引用的基表中的一个。
有关可更新视图的更多信息，请
参见CREATEVIEW。
rowset_function_limited
OPENQUERY或OPENROWSET函数，视提供程序功能而定。
有关
提供程序所需功能的更多信息，请参见OLEDB提供程序的
UPDATE和DELETE语句要求。
有关行集函数的更多信息，请参
见OPENQUERY和OPENROWSET。
SET
指定要更新的列或变量名称的列表。
column_name
含有要更改数据的列的名称。
column_name必须驻留于UPDATE
子句中所指定的表或视图中。
标识列不能进行更新。
如果指定了限定的列名称，限定符必须同UPDATE子句中的表或
视图的名称相匹配。
例如，下面的内容有效：
UPDATEauthors
SETauthors.au_fname='Annie'
WHEREau_fname='Anne'
FROM子句中指定的表的别名不能作为SETcolumn_name子
句中的限定符使用。
例如，下面的内容无效：
UPDATEtitles
SETt.ytd_sales=t.ytd_sales+s.qty
FROMtitlest,saless
WHEREt.title_id=s.title_id
ANDs.ord_date=（SELECTMAX（sales.ord_date）
FROMsales）
若要使上例合法，请从列名中删除别名t。
UPDATEtitles
SETytd_sales=t.ytd_sales+s.qty
FROMtitlest,saless
WHEREt.title_id=s.title_id
ANDs.ord_date=（SELECTMAX（sales.ord_date）
FROMsales）
expression
变量、字面值、表达式或加上括弧的返回单个值的subSELECT
语句。
expression返回的值将替换column_name或
@variable中的现有值。
DEFAULT
指定使用对列定义的默认值替换列中的现有值。
如果该列没有默认值
并且定义为允许空值，这也可用来将列更改为NULL。
@variable
已声明的变量，该变量将设置为expression所返回的值。
SET@variable=column=expression将变量设置为
与列相同的值。
这与SET@variable=column,column
=expression不同，后者将变量设置为列更新前的值。
FROM
指定用表来为更新操作提供准则。
有关更多信息，请参见FROM。
table_name[[AS]table_alias]
为更新操作提供准则的表的名称。
如果所更新表与FROM子句中的表相同，并且在FROM子句
中对该表只有一个引用，则指定或不指定table_alias均可。
如
果所更新表在FROM子句中出现了不止一次，则对该表的一个（且
仅仅一个）引用不能指定表的别名。
FROM子句中对该表的所有其它
引用都必须包含表的别名。
view_name[[AS]table_alias]
为更新操作提供准则的视图的名称。
带INSTEADOFUPDATE
触发器的视图不能是含有FROM子句的UPDATE的目标。
WITH（[...n]）
为源表指定一个或多个表提示。
有关表提示的信息，请参见本卷
的"FROM"。
rowset_function[[AS]table_alias]
任意行集函数的名称和可选别名。
有关行集函数列表的信息，请参见
行集函数。
derived_table
是从数据库中检索行的子查询。
derived_table用作对外部查询的
输入。
column_alias
替换结果集内列名的可选别名。
在选择列表中放入每个列的一个别
名，并将整个列别名列表用圆括号括起来。
由两个或更多表的积组成的结果集，例如：
SELECT*
FROMtab1LEFTOUTERJOINtab2ONtab1.c3=
tab2.c3
RIGHTOUTERJOINtab3LEFTOUTERJOIN
tab4
ONtab3.c1=tab4.c1
ONtab2.c3=tab4.c3
对于多个CROSS联接，请使用圆括号来更改联接的自然顺序。
指定联接操作的类型。
INNER
指定返回所有相匹配的行对。
废弃两个表中不匹配的行。
如果未指定
联接类型，则这是默认设置。
LEFT[OUTER]
指定除所有由内联接返回的行外，所有来自左表的不符合指定条件的
行也包含在结果集内。
来自左表的输出列设置为NULL。
RIGHT[OUTER]
指定除所有由内联接返回的行外，所有来自右表的不符合指定条件的
行也包含在结果集内。
来自右表的输出列设置为NULL。
FULL[OUTER]
如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包
含该行，并且将与另一个表对应的输出列设置为NULL。
除此之外，
结果集中还包含通常由内联接返回的所有行。
指定联接提示或执行算法。
如果指定了，也必须明确
指定INNER、LEFT、RIGHT或FULL。
有关联接提示的更多信
息，请参见FROM。
JOIN
表示联接所指定的表或视图。
ON
指定联接所基于的条件。
尽管经常使用列和比较运算符，但此条件可
指定任何谓词，例如：
FROMSuppliersJOINProducts
ON（Suppliers.SupplierID=
Products.SupplierID）
当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，
如果数据类型不一致，则这些列要么必须相互兼容，要么是
Microsoft®SQLServer?
能够隐性转换的类型。
如
果数据类型不能隐式转换，则条件必须使用CAST函数显式转换数
据类型。
有关搜索条件和谓词的更多信息，请参见搜索条件。
CROSSJOIN
指定两个表的矢量积。
返回同样的行，就像要联接的表只列于
FROM子句中，并且未指定WHERE子句。
WHERE
指定条件来限定所更新的行。
根据所使用的WHERE子句的形
式，有两种更新形式：
搜索更新指定搜索条件来限定要删除的行。
定位更新使用CURRENTOF子句指定游标。
更新操作发生在游
标的当前位置。
为要更新行指定需满足的条件。
搜索条件也可以是联接所基于的条
件。
对搜索条件中可以包含的谓词数量没有限制。
有关谓词和搜索条件
的更多信息，请参见搜索条件。
CURRENTOF
指定更新在指定游标的当前位置进行。
Top
7楼Yang_（扬帆破浪）回复于2002-08-1521:
37:
04得分0续：
GLOBAL
指定cursor_name指的是全局游标。
cursor_name
要从中进行提取的开放游标的名称。
如果同时存在名为
cursor_name的全局游标和局部游标，则在指定了GLOBAL时，
cursor_name指的是全局游标。
如果未指定GLOBAL，则
cursor_name指局部游标。
游标必须允许更新。
cursor_variable_name
游标变量的名称。
cursor_variable_name必须引用允许更新的游
标。
OPTION（[,...n]）
指定优化程序提示用于自定义SQLServer的语句处理。
{HASH|ORDER}GROUP
指定在查询的GROUPBY或COMPUTE子句中指定的聚合
使用哈希或排列。
{LOOP|MERGE|HASH|}JOIN
指定在整个查询中所有的联接操作由循环联接、合并联接或哈希联接
来完成。
如果指定了不止一个联接提示，则查询优化器为允许的联接选
择开销最少的联接策略。
如果在同一个查询中也为特定表对指定了联接
提示，则该提示在两表的联接中优先。
{MERGE|HASH|CONCAT}UNION
指定所有的UNION操作通过合并、哈希或串联UNION集合
来完成。
如果指定了不止一个UNION提示，查询优化器就会从这
些指定的提示中选择开销最少的策略。
说明如果在FROM子句中亦为任何特定联接表对指定了联
接提示，则该提示优先于任何OPTION子句中指定的联接提示。
FASTnumber_rows
指定对查询进行优化，以便快速检索第一个number_rows（非负整
数）。
在第一个number_rows返回后，查询继续进行并生成完整
的结果集。
FORCEORDER
指定查询语法所指示的联接顺序在查询优化过程中予以保留。
MAXDOPnumber
只对指定了sp_configure的maxdegreeof
parallelism配置选项的查询替代该选项。
当使用MAXDOP查询
提示时，所有和maxdegreeofparallelism配置选项一
起使用的语义规则均适用。
有关更多信息，请参见maxdegree
ofparallelism选项。
ROBUSTPLAN
强制查询优化器尝试执行一个计划，该计划以性能为代价获得最大可
能的行大小。
如果没有可行的计划，则查询优化器返回错误，而不是将
错误检测延迟至查询执行。
行可能包含长度可变的列；SQLServer
允许定义最大可能大小超出SQLServer处理能力的行。
通常，
应用程序存储实际大小在SQLServer处理能力范围内的行，而
不管最大可能大小。
如果SQLServer遇到过长的行，则返回执
行错误。
KEEPPLAN
强制查询优化器对查询放宽估计的重新编译阈值。
当对表中索引列的
更改（更新、删除或插入）达到估计数目时查询会自动重新编译，该估
计数目即为重新编译阈值。
指定KEEPPLAN将确保当表有多个
更新时不会频繁地对查询进行重新编译。
注释
仅当所修改的表是table变量时，用户定义的函数的主体中才
允许使用UPDATE语句。
table变量在其作用域内可以像常规表一样访问。
这样，table
变量可作为一个表来使用，在该表中数据用UPDATE语句进行更
新。
用OPENDATASOURCE函数构造的、作为服务器名称部分的一个四
段名称，在UPDATE语句中可以出现表名的任何地方都可作为表源
使用。
如果对行的更新违反了某个约束或规则，或违反了对列的NULL
设置，或者新值是不兼容的数据类型，则取消该语句、返回错误并且不
更新任何记录。
当UPDATE语句在表达式取值过程中遇到算术错误（溢出、被零
除或域错误）时，则不进行更新。
批处理的剩余部分不再执行，并且返
回错误信息。
如果对参与聚集索引的一列或多列的更新导致聚集索引和行的大小超
过8,060字节，则更新失败并且返回错误信息。
当对表的UPDATE操作定义INSTEAD-OF触发器时，将执行
触发器而不执行UPDATE语句。
SQLServer以前的版本只支
持在UPDATE和其它数据修改语句中定义AFTER触发器。
当更新查询既更新聚集键又更新一个或多个text、image或
Unicode列时，如果可以更改不止一行，则更新操作失败，SQL
Server返回错误信息。
用UPDATE修改text、ntext或image列时将对列进
行初始化，向其指派有效文本指针，并且分配至少一个数据页（除非用
NULL更新该列）。
说明UPDATE语句将记入日志。
如果要替换或修改大块的
text、ntext或image数据，请使用WRITETEXT或
UPDATETEXT语句而不要使用UPDATE语句。
WRITETEXT和
UPDATETEXT语句（根据默认）不记入日志。
所有的char和nchar列向右填充至定义长度。
对于用于远程表以及本地和远程分区视图的UPDATE语句，忽略
SETROWCOUNT选项的设置。
如果ANSI_PADDING设置为OFF，则会从插入varchar
和nvarchar列的数据中删除所有尾随空格，但只包含空格的字符
串除外。
这些字符串被截断为空字符串。
如果ANSI_PADDING设置
为ON，则插入尾随空格。
MicrosoftSQLServerODBC驱
动程序和用于SQLServer的OLEDB提供程序自动对每
个连接设置ANSI_PADDINGON。
这可在ODBC数据源中进行配
置，或者通过设置连接特性或属性进行设置。
使用WHERECURRENTOF子句的定位更新将在游标的当前位
置更新单行。
这比使用WHERE子句限定要
更新的行的搜索更新更为精确。
当搜索条件不唯一标识一行时，搜索更
新将修改多行。
如果UPDATE语句包含了未指定每个所更新列的位置只有一个可
用值的FROM子句（换句话说，如果UPDATE语句是不确定性
的），则其结果将不明确。
例如，对于下面脚本中的UPDATE语
句，表s中的两行都满足UPDATE语句中的FROM子句
的限定条件，但是将用s的哪一行来更新表t内的行是不明
确的。
CREATETABLEs（ColAINT,ColBDECIMAL（10,3））
GO
CREATETABLEt（ColAINTPRIMARYKEY,ColB
DECIMAL（10,3））
GO
INSERTINTOsVALUES（1,10.0）
INSERTINTOsVALUES（1,20.0）
INSERTINTOtVALUES（1,0.0）
GO
UPDATEt
SETt.ColB=t.ColB+s.ColB
FROMtINNERJOINsON（t.ColA=s.ColA）
GO
当组合FROM和WHERECURRENTOF子句时，可能发生
同样的问题。
在本例中，表t2中的两行都满足UPDATE语句
中的FROM子句的限定条件。
将用表t2的哪一行来更新表
t1中的行是不明确的。
CREATETABLEt1（c1INTPRIMARYKEY,c2INT）
GO
CREATETABLEt2（d1INTPRIMARYKEY,d2INT）
GO
INSERTINTOt1VALUES（1,10）
INSERTINTOt2VALUES（1,20）
INSERTINTOt2VALUES（2,30）
go
DECLAREabcCURSORLOCALFOR
SELECT*FROMt1
OPENabc
FETCHabc
UPDATEt1SETc2=c2+d2
FROMt2
WHERECURRENTOFabc
GO
设置变量和列
变量名可用于UPDATE语句来显示受影响的旧值和新值。
这种方
法应该仅用在UPDATE语句只影响单个记录时；如果UPDATE
语句影响多个记录，则变量只含有所更新行中的一行的值。
权限
UPDATE权限默认授予sysadmin固定服务器角色成员、
db_owner和db_datawriter固定数据库角色成员以及表的所有
者。
sysadmin、db_owner和db_securityadmin角色的成员和
表所有者可以将权限转让给其他用户。
如果UPDATE语句包含WHERE子句，或SET子句中的
expression使用了表中的某个列，则还要求所更新表的SELECT
权限。
示例
A.使用简单的UPDATE
下列示例说明如果从UPDATE语句中去除WHERE子句，所
有的行会受到什么影响。
下面这个例子说明，如果表publishers中的所有出版社将总部
搬迁到佐治亚州的亚特兰大市，表publishers如何更新。
UPDATEpublishers
SETcity='Atlanta',state='GA'
本示例将所有出版商的名字变为NULL。
UPDATEpublishers
SETpub_name=NULL
也可以在更新中使用计算值。
本示例将表titles中的所有价格
加倍。
UPDATEtitles
SETprice=price*2
B.把WHERE子句和UPDATE语句一起使用
WHERE子句指定要更新的行例如，在下面这个虚构的事件中，北加
利福尼亚更名为Pacifica（缩写为PC），而奥克兰的市民投票决
定将其城市的名字改为BayCity。
这个例子说明如何为奥克兰市
以前的所有居民（他们的地址已经过时）更新表authors。
UPDATEauthors
SETstate='PC',city='
升级会员 