5,388
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
newyj 2001-04-29
- 打赏
- 举报
可以把sql server 的中文帮助发给你（30M)
newyj 2001-04-29
- 打赏
- 举报
UPDATE
更改表中的现有数据。
语法
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 子句中所指定的表或视图中。标识列不能进行更新。
如果指定了限定的列名称，限定符必须同 UPDATE 子句中的表或视图的名称相匹配。例如，下面的内容有效：
UPDATE authors
SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
FROM 子句中指定的表的别名不能作为 SET column_name 子句中的限定符使用。例如，下面的内容无效：
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
若要使上例合法，请从列名中删除别名 t。
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
expression
变量、字面值、表达式或加上括弧的返回单个值的 subSELECT 语句。expression 返回的值将替换 column_name 或 @variable 中的现有值。
DEFAULT
指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许空值，这也可用来将列更改为 NULL。
@variable
已声明的变量，该变量将设置为 expression 所返回的值。
SET @variable = column = expression 将变量设置为与列相同的值。这与 SET @variable = column, column = expression 不同，后者将变量设置为列更新前的值。
FROM < table_source >
指定用表来为更新操作提供准则。有关更多信息，请参见 FROM。
table_name [[AS] table_alias ]
为更新操作提供准则的表的名称。
如果所更新表与 FROM 子句中的表相同，并且在 FROM 子句中对该表只有一个引用，则指定或不指定 table_alias 均可。如果所更新表在 FROM 子句中出现了不止一次，则对该表的一个（且仅仅一个）引用不能指定表的别名。FROM 子句中对该表的所有其它引用都必须包含表的别名。
view_name [ [ AS ] table_alias ]
为更新操作提供准则的视图的名称。带 INSTEAD OF UPDATE 触发器的视图不能是含有 FROM 子句的 UPDATE 的目标。
WITH ( < table_hint > [ ...n ] )
为源表指定一个或多个表提示。有关表提示的信息，请参见本卷的"FROM"。
rowset_function [ [AS] table_alias ]
任意行集函数的名称和可选别名。有关行集函数列表的信息，请参见行集函数。
derived_table
是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。
column_alias
替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。
<joined_table>
由两个或更多表的积组成的结果集，例如：
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。
<join_type>
指定联接操作的类型。
INNER
指定返回所有相匹配的行对。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。
LEFT [OUTER]
指定除所有由内联接返回的行外，所有来自左表的不符合指定条件的行也包含在结果集内。来自左表的输出列设置为 NULL。
RIGHT [OUTER]
指定除所有由内联接返回的行外，所有来自右表的不符合指定条件的行也包含在结果集内。来自右表的输出列设置为 NULL。
FULL [OUTER]
如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联接返回的所有行。
<join_hint>
指定联接提示或执行算法。如果指定了 <join_hint>，也必须明确指定 INNER、LEFT、RIGHT 或 FULL。有关联接提示的更多信息，请参见 FROM。
JOIN
表示联接所指定的表或视图。
ON <search_condition>
指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，例如：
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列要么必须相互兼容，要么是 Microsoft® SQL Server™ 能够隐性转换的类型。如果数据类型不能隐式转换，则条件必须使用 CAST 函数显式转换数据类型。
有关搜索条件和谓词的更多信息，请参见搜索条件。
CROSS JOIN
指定两个表的矢量积。返回同样的行，就像要联接的表只列于 FROM 子句中，并且未指定 WHERE 子句。
WHERE
指定条件来限定所更新的行。根据所使用的 WHERE 子句的形式，有两种更新形式：
搜索更新指定搜索条件来限定要删除的行。
定位更新使用 CURRENT OF 子句指定游标。更新操作发生在游标的当前位置。
<search_condition>
为要更新行指定需满足的条件。搜索条件也可以是联接所基于的条件。对搜索条件中可以包含的谓词数量没有限制。有关谓词和搜索条件的更多信息，请参见搜索条件。
CURRENT OF
指定更新在指定游标的当前位置进行。
GLOBAL
指定 cursor_name 指的是全局游标。
cursor_name
要从中进行提取的开放游标的名称。如果同时存在名为 cursor_name 的全局游标和局部游标，则在指定了 GLOBAL 时，cursor_name 指的是全局游标。如果未指定 GLOBAL，则 cursor_name 指局部游标。游标必须允许更新。
cursor_variable_name
游标变量的名称。cursor_variable_name 必须引用允许更新的游标。
OPTION ( < query_hint > [ ,...n ] )
指定优化程序提示用于自定义 SQL Server 的语句处理。
{ HASH | ORDER } GROUP
指定在查询的 GROUP BY 或 COMPUTE 子句中指定的聚合使用哈希或排列。
{ LOOP | MERGE | HASH |} JOIN
指定在整个查询中所有的联接操作由循环联接、合并联接或哈希联接来完成。如果指定了不止一个联接提示，则查询优化器为允许的联接选择开销最少的联接策略。如果在同一个查询中也为特定表对指定了联接提示，则该提示在两表的联接中优先。
{ MERGE | HASH | CONCAT } UNION
指定所有的 UNION 操作通过合并、哈希或串联 UNION 集合来完成。如果指定了不止一个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。
说明 如果在 FROM 子句中亦为任何特定联接表对指定了联接提示，则该提示优先于任何 OPTION 子句中指定的联接提示。
FAST number_rows
指定对查询进行优化，以便快速检索第一个 number_rows（非负整数）。在第一个 number_rows 返回后，查询继续进行并生成完整的结果集。
FORCE ORDER
指定查询语法所指示的联接顺序在查询优化过程中予以保留。
MAXDOP number
只对指定了 sp_configure 的 max degree of parallelism 配置选项的查询替代该选项。当使用 MAXDOP 查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。有关更多信息，请参见 max degree of parallelism 选项。
ROBUST PLAN
强制查询优化器尝试执行一个计划，该计划以性能为代价获得最大可能的行大小。如果没有可行的计划，则查询优化器返回错误，而不是将错误检测延迟至查询执行。行可能包含长度可变的列；SQL Server 允许定义最大可能大小超出 SQL Server 处理能力的行。通常，应用程序存储实际大小在 SQL Server 处理能力范围内的行，而不管最大可能大小。如果 SQL Server 遇到过长的行，则返回执行错误。
KEEP PLAN
强制查询优化器对查询放宽估计的重新编译阈值。当对表中索引列的更改（更新、删除或插入）达到估计数目时查询会自动重新编译，该估计数目即为重新编译阈值。指定 KEEP PLAN 将确保当表有多个更新时不会频繁地对查询进行重新编译。
注释
仅当所修改的表是 table 变量时，用户定义的函数的主体中才允许使用 UPDATE 语句。
table 变量在其作用域内可以像常规表一样访问。这样，table 变量可作为一个表来使用，在该表中数据用 UPDATE 语句进行更新。
用 OPENDATASOURCE 函数构造的、作为服务器名称部分的一个四段名称，在 UPDATE 语句中可以出现表名的任何地方都可作为表源使用。
如果对行的更新违反了某个约束或规则，或违反了对列的 NULL 设置，或者新值是不兼容的数据类型，则取消该语句、返回错误并且不更新任何记录。
当 UPDATE 语句在表达式取值过程中遇到算术错误（溢出、被零除或域错误）时，则不进行更新。批处理的剩余部分不再执行，并且返回错误信息。
如果对参与聚集索引的一列或多列的更新导致聚集索引和行的大小超过 8,060 字节，则更新失败并且返回错误信息。
当对表的 UPDATE 操作定义 INSTEAD-OF 触发器时，将执行触发器而不执行 UPDATE 语句。SQL Server 以前的版本只支持在 UPDATE 和其它数据修改语句中定义 AFTER 触发器。
当更新查询既更新聚集键又更新一个或多个 text、image 或 Unicode 列时，如果可以更改不止一行，则更新操作失败，SQL Server 返回错误信息。
用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化，向其指派有效文本指针，并且分配至少一个数据页（除非用 NULL 更新该列）。
说明 UPDATE 语句将记入日志。如果要替换或修改大块的 text、ntext 或 image 数据，请使用 WRITETEXT 或 UPDATETEXT 语句而不要使用 UPDATE 语句。WRITETEXT 和 UPDATETEXT 语句（根据默认）不记入日志。
所有的 char 和 nchar 列向右填充至定义长度。
对于用于远程表以及本地和远程分区视图的 UPDATE 语句，忽略 SET ROWCOUNT 选项的设置。
如果 ANSI_PADDING 设置为 OFF，则会从插入 varchar 和 nvarchar 列的数据中删除所有尾随空格，但只包含空格的字符串除外。这些字符串被截断为空字符串。如果 ANSI_PADDING 设置为 ON，则插入尾随空格。Microsoft SQL Server ODBC 驱动程序和用于 SQL Server 的 OLE DB 提供程序自动对每个连接设置 ANSI_PADDING ON。这可在 ODBC 数据源中进行配置，或者通过设置连接特性或属性进行设置。
使用 WHERE CURRENT OF 子句的定位更新将在游标的当前位置更新单行。这比使用 WHERE <search_condition> 子句限定要更新的行的搜索更新更为精确。当搜索条件不唯一标识一行时，搜索更新将修改多行。
如果 UPDATE 语句包含了未指定每个所更新列的位置只有一个可用值的 FROM 子句（换句话说，如果 UPDATE 语句是不确定性的），则其结果将不明确。例如，对于下面脚本中的 UPDATE 语句，表 s 中的两行都满足 UPDATE 语句中的 FROM 子句的限定条件，但是将用 s 的哪一行来更新表 t 内的行是不明确的。
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
当组合 FROM 和 WHERE CURRENT OF 子句时，可能发生同样的问题。在本例中，表 t2 中的两行都满足 UPDATE 语句中的 FROM 子句的限定条件。将用表 t2 的哪一行来更新表 t1 中的行是不明确的。
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go
DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1
OPEN abc
FETCH abc
UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
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 如何更新。
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
本示例将所有出版商的名字变为 NULL。
UPDATE publishers
SET pub_name = NULL
也可以在更新中使用计算值。本示例将表 titles 中的所有价格加倍。
UPDATE titles
SET price = price * 2
B.把 WHERE 子句和 UPDATE 语句一起使用
WHERE 子句指定要更新的行例如，在下面这个虚构的事件中，北加利福尼亚更名为 Pacifica（缩写为 PC），而奥克兰的市民投票决定将其城市的名字改为 Bay City。这个例子说明如何为奥克兰市以前的所有居民（他们的地址已经过时）更新表 authors。
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
必须编写另一个语句来更改北加利福尼亚其它城市的居民所在的州名。
C.通过 UPDATE 语句使用来自另一个表的信息
本示例修改表 titles 中的 ytd_sales 列，以反映表 sales 中的最新销售记录。
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
这个例子假定，一种特定的商品在特定的日期只记录一批销售量，而且更新是最新的。如果不是这样（即如果一种特定的商品在同一天可以记录不止一批销售量），这里所示的例子将出错。例子可正确执行，但是每种商品只用一批销售量进行更新，而不管那一天实际销售了多少批。这是因为一个 UPDATE 语句从不会对同一行更新两次。
对于特定的商品在同一天可销售不止一批的情况，每种商品的所有销售量必须在 UPDATE 语句中合计在一起，如下例所示：
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. 将 UPDATE 语句与 SELECT 语句中的 TOP 子句一起使用
这个例子对来自表 authors 的前十个作者的 state 列进行更新。
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
请参见
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
游标
DELETE
INSERT
SET ROWCOUNT
文本和图像函数
©1988-2000 Microsoft Corporation。保留所有权利。
更改表中的现有数据。
语法
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 子句中所指定的表或视图中。标识列不能进行更新。
如果指定了限定的列名称，限定符必须同 UPDATE 子句中的表或视图的名称相匹配。例如，下面的内容有效：
UPDATE authors
SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
FROM 子句中指定的表的别名不能作为 SET column_name 子句中的限定符使用。例如，下面的内容无效：
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
若要使上例合法，请从列名中删除别名 t。
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
expression
变量、字面值、表达式或加上括弧的返回单个值的 subSELECT 语句。expression 返回的值将替换 column_name 或 @variable 中的现有值。
DEFAULT
指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许空值，这也可用来将列更改为 NULL。
@variable
已声明的变量，该变量将设置为 expression 所返回的值。
SET @variable = column = expression 将变量设置为与列相同的值。这与 SET @variable = column, column = expression 不同，后者将变量设置为列更新前的值。
FROM < table_source >
指定用表来为更新操作提供准则。有关更多信息，请参见 FROM。
table_name [[AS] table_alias ]
为更新操作提供准则的表的名称。
如果所更新表与 FROM 子句中的表相同，并且在 FROM 子句中对该表只有一个引用，则指定或不指定 table_alias 均可。如果所更新表在 FROM 子句中出现了不止一次，则对该表的一个（且仅仅一个）引用不能指定表的别名。FROM 子句中对该表的所有其它引用都必须包含表的别名。
view_name [ [ AS ] table_alias ]
为更新操作提供准则的视图的名称。带 INSTEAD OF UPDATE 触发器的视图不能是含有 FROM 子句的 UPDATE 的目标。
WITH ( < table_hint > [ ...n ] )
为源表指定一个或多个表提示。有关表提示的信息，请参见本卷的"FROM"。
rowset_function [ [AS] table_alias ]
任意行集函数的名称和可选别名。有关行集函数列表的信息，请参见行集函数。
derived_table
是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。
column_alias
替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。
<joined_table>
由两个或更多表的积组成的结果集，例如：
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。
<join_type>
指定联接操作的类型。
INNER
指定返回所有相匹配的行对。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。
LEFT [OUTER]
指定除所有由内联接返回的行外，所有来自左表的不符合指定条件的行也包含在结果集内。来自左表的输出列设置为 NULL。
RIGHT [OUTER]
指定除所有由内联接返回的行外，所有来自右表的不符合指定条件的行也包含在结果集内。来自右表的输出列设置为 NULL。
FULL [OUTER]
如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联接返回的所有行。
<join_hint>
指定联接提示或执行算法。如果指定了 <join_hint>，也必须明确指定 INNER、LEFT、RIGHT 或 FULL。有关联接提示的更多信息，请参见 FROM。
JOIN
表示联接所指定的表或视图。
ON <search_condition>
指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，例如：
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
当条件指定列时，列不一定必须具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列要么必须相互兼容，要么是 Microsoft® SQL Server™ 能够隐性转换的类型。如果数据类型不能隐式转换，则条件必须使用 CAST 函数显式转换数据类型。
有关搜索条件和谓词的更多信息，请参见搜索条件。
CROSS JOIN
指定两个表的矢量积。返回同样的行，就像要联接的表只列于 FROM 子句中，并且未指定 WHERE 子句。
WHERE
指定条件来限定所更新的行。根据所使用的 WHERE 子句的形式，有两种更新形式：
搜索更新指定搜索条件来限定要删除的行。
定位更新使用 CURRENT OF 子句指定游标。更新操作发生在游标的当前位置。
<search_condition>
为要更新行指定需满足的条件。搜索条件也可以是联接所基于的条件。对搜索条件中可以包含的谓词数量没有限制。有关谓词和搜索条件的更多信息，请参见搜索条件。
CURRENT OF
指定更新在指定游标的当前位置进行。
GLOBAL
指定 cursor_name 指的是全局游标。
cursor_name
要从中进行提取的开放游标的名称。如果同时存在名为 cursor_name 的全局游标和局部游标，则在指定了 GLOBAL 时，cursor_name 指的是全局游标。如果未指定 GLOBAL，则 cursor_name 指局部游标。游标必须允许更新。
cursor_variable_name
游标变量的名称。cursor_variable_name 必须引用允许更新的游标。
OPTION ( < query_hint > [ ,...n ] )
指定优化程序提示用于自定义 SQL Server 的语句处理。
{ HASH | ORDER } GROUP
指定在查询的 GROUP BY 或 COMPUTE 子句中指定的聚合使用哈希或排列。
{ LOOP | MERGE | HASH |} JOIN
指定在整个查询中所有的联接操作由循环联接、合并联接或哈希联接来完成。如果指定了不止一个联接提示，则查询优化器为允许的联接选择开销最少的联接策略。如果在同一个查询中也为特定表对指定了联接提示，则该提示在两表的联接中优先。
{ MERGE | HASH | CONCAT } UNION
指定所有的 UNION 操作通过合并、哈希或串联 UNION 集合来完成。如果指定了不止一个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。
说明 如果在 FROM 子句中亦为任何特定联接表对指定了联接提示，则该提示优先于任何 OPTION 子句中指定的联接提示。
FAST number_rows
指定对查询进行优化，以便快速检索第一个 number_rows（非负整数）。在第一个 number_rows 返回后，查询继续进行并生成完整的结果集。
FORCE ORDER
指定查询语法所指示的联接顺序在查询优化过程中予以保留。
MAXDOP number
只对指定了 sp_configure 的 max degree of parallelism 配置选项的查询替代该选项。当使用 MAXDOP 查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。有关更多信息，请参见 max degree of parallelism 选项。
ROBUST PLAN
强制查询优化器尝试执行一个计划，该计划以性能为代价获得最大可能的行大小。如果没有可行的计划，则查询优化器返回错误，而不是将错误检测延迟至查询执行。行可能包含长度可变的列；SQL Server 允许定义最大可能大小超出 SQL Server 处理能力的行。通常，应用程序存储实际大小在 SQL Server 处理能力范围内的行，而不管最大可能大小。如果 SQL Server 遇到过长的行，则返回执行错误。
KEEP PLAN
强制查询优化器对查询放宽估计的重新编译阈值。当对表中索引列的更改（更新、删除或插入）达到估计数目时查询会自动重新编译，该估计数目即为重新编译阈值。指定 KEEP PLAN 将确保当表有多个更新时不会频繁地对查询进行重新编译。
注释
仅当所修改的表是 table 变量时，用户定义的函数的主体中才允许使用 UPDATE 语句。
table 变量在其作用域内可以像常规表一样访问。这样，table 变量可作为一个表来使用，在该表中数据用 UPDATE 语句进行更新。
用 OPENDATASOURCE 函数构造的、作为服务器名称部分的一个四段名称，在 UPDATE 语句中可以出现表名的任何地方都可作为表源使用。
如果对行的更新违反了某个约束或规则，或违反了对列的 NULL 设置，或者新值是不兼容的数据类型，则取消该语句、返回错误并且不更新任何记录。
当 UPDATE 语句在表达式取值过程中遇到算术错误（溢出、被零除或域错误）时，则不进行更新。批处理的剩余部分不再执行，并且返回错误信息。
如果对参与聚集索引的一列或多列的更新导致聚集索引和行的大小超过 8,060 字节，则更新失败并且返回错误信息。
当对表的 UPDATE 操作定义 INSTEAD-OF 触发器时，将执行触发器而不执行 UPDATE 语句。SQL Server 以前的版本只支持在 UPDATE 和其它数据修改语句中定义 AFTER 触发器。
当更新查询既更新聚集键又更新一个或多个 text、image 或 Unicode 列时，如果可以更改不止一行，则更新操作失败，SQL Server 返回错误信息。
用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化，向其指派有效文本指针，并且分配至少一个数据页（除非用 NULL 更新该列）。
说明 UPDATE 语句将记入日志。如果要替换或修改大块的 text、ntext 或 image 数据，请使用 WRITETEXT 或 UPDATETEXT 语句而不要使用 UPDATE 语句。WRITETEXT 和 UPDATETEXT 语句（根据默认）不记入日志。
所有的 char 和 nchar 列向右填充至定义长度。
对于用于远程表以及本地和远程分区视图的 UPDATE 语句，忽略 SET ROWCOUNT 选项的设置。
如果 ANSI_PADDING 设置为 OFF，则会从插入 varchar 和 nvarchar 列的数据中删除所有尾随空格，但只包含空格的字符串除外。这些字符串被截断为空字符串。如果 ANSI_PADDING 设置为 ON，则插入尾随空格。Microsoft SQL Server ODBC 驱动程序和用于 SQL Server 的 OLE DB 提供程序自动对每个连接设置 ANSI_PADDING ON。这可在 ODBC 数据源中进行配置，或者通过设置连接特性或属性进行设置。
使用 WHERE CURRENT OF 子句的定位更新将在游标的当前位置更新单行。这比使用 WHERE <search_condition> 子句限定要更新的行的搜索更新更为精确。当搜索条件不唯一标识一行时，搜索更新将修改多行。
如果 UPDATE 语句包含了未指定每个所更新列的位置只有一个可用值的 FROM 子句（换句话说，如果 UPDATE 语句是不确定性的），则其结果将不明确。例如，对于下面脚本中的 UPDATE 语句，表 s 中的两行都满足 UPDATE 语句中的 FROM 子句的限定条件，但是将用 s 的哪一行来更新表 t 内的行是不明确的。
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
当组合 FROM 和 WHERE CURRENT OF 子句时，可能发生同样的问题。在本例中，表 t2 中的两行都满足 UPDATE 语句中的 FROM 子句的限定条件。将用表 t2 的哪一行来更新表 t1 中的行是不明确的。
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go
DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1
OPEN abc
FETCH abc
UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
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 如何更新。
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
本示例将所有出版商的名字变为 NULL。
UPDATE publishers
SET pub_name = NULL
也可以在更新中使用计算值。本示例将表 titles 中的所有价格加倍。
UPDATE titles
SET price = price * 2
B.把 WHERE 子句和 UPDATE 语句一起使用
WHERE 子句指定要更新的行例如，在下面这个虚构的事件中，北加利福尼亚更名为 Pacifica（缩写为 PC），而奥克兰的市民投票决定将其城市的名字改为 Bay City。这个例子说明如何为奥克兰市以前的所有居民（他们的地址已经过时）更新表 authors。
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
必须编写另一个语句来更改北加利福尼亚其它城市的居民所在的州名。
C.通过 UPDATE 语句使用来自另一个表的信息
本示例修改表 titles 中的 ytd_sales 列，以反映表 sales 中的最新销售记录。
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
这个例子假定，一种特定的商品在特定的日期只记录一批销售量，而且更新是最新的。如果不是这样（即如果一种特定的商品在同一天可以记录不止一批销售量），这里所示的例子将出错。例子可正确执行，但是每种商品只用一批销售量进行更新，而不管那一天实际销售了多少批。这是因为一个 UPDATE 语句从不会对同一行更新两次。
对于特定的商品在同一天可销售不止一批的情况，每种商品的所有销售量必须在 UPDATE 语句中合计在一起，如下例所示：
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. 将 UPDATE 语句与 SELECT 语句中的 TOP 子句一起使用
这个例子对来自表 authors 的前十个作者的 state 列进行更新。
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
请参见
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
游标
DELETE
INSERT
SET ROWCOUNT
文本和图像函数
©1988-2000 Microsoft Corporation。保留所有权利。
newyj 2001-04-29
- 打赏
- 举报
DELETE
从表中删除行。
语法
DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE
{ < search_condition >
| { [ 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
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
参数
FROM
是可选的关键字，可用在 DELETE 关键字与目标 table_name、view_name 或 rowset_function_limited 之间。
table_name
是要从其中删除行的表的名称。
在其作用域内的 table 变量、或是将 OPENDATASOURCE 函数作为服务器名称的由四部分组成的表名（或视图名称）还可以在 DELETE 语句中作为表源使用。
WITH (<table_hint_limited> [...n])
指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。有关表提示的更多信息，请参见 FROM。
view_name
是视图名称。在视图的 FROM 子句中，view_name 引用的视图必须可更新且正确引用一个基表。有关可更新视图的更多信息，请参见 CREATE VIEW。
说明 如果表或视图存在于另一个数据库内或有一个不同于当前用户的所有者，则使用格式为 server_name.database.[owner].object_name 的由四部分组成的合法名称。有关更多信息，请参见 Transact-SQL 语法规则。
rowset_function_limited
OPENQUERY 或 OPENROWSET 函数，视提供程序功能而定。有关提供程序所需功能的更多信息，请参见 OLE DB 提供程序的 UPDATE 和 DELETE 语句要求。有关行集函数的更多信息，请参见 OPENQUERY 和 OPENROWSET。
FROM < table_source >
指定附加的 FROM 子句。这个对 DELETE 的 Transact-SQL 扩展使您得以从 <table_sources> 指定数据，并从第一个 FROM 子句内的表中删除相应的行。
这个扩展指定联接，可在 WHERE 子句中取代子查询来标识要删除的行。
table_name [[AS] table_alias ]
是为删除操作提供标准值的表名。
view_name [ [ AS ] table_alias ]
是为删除操作提供标准值的视图名称。带 INSTEAD OF UPDATE 触发器的视图不能是含有 FROM 子句的 UPDATE 的目标。
WITH (<table_hint>
指定一个或更多表提示。有关表提示的更多信息，请参见 FROM。
rowset_function [ [AS] table_alias ]
是行集函数名和可选别名。有关行集函数列表的更多信息，请参见行集函数。
derived_table [AS] table_alias
是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。
column_alias
替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。
<joined_table>
由两个或更多表的积组成的结果集，例如：
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。
<join_type>
指定联接操作的类型。
INNER
指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。
LEFT [OUTER]
指定在结果集内包含左表中所有不满足指定条件的行，并将右表中的输出列设置为 NULL 以作为对内联接所返回的所有行的补充。
RIGHT [OUTER]
指定在结果集内包含右表中所有不满足指定条件的行，并将左表中的输出列设置为 NULL 以作为对内联接所返回的所有行的补充。
FULL [OUTER]
如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联接返回的所有行。
JOIN
是表示在删除操作中使用 SQL-92 式联接的关键字。
ON <search_condition>
指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，例如：
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
当条件指定列时，列不必具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列必须相互兼容或是 Microsoft® SQL Server™ 能够隐性转换的类型。如果数据类型不能隐性转换，则条件必须使用 CAST 函数显式转换数据类型。
有关搜索条件和谓词的更多信息，请参见搜索条件。
CROSS JOIN
指定两个表的矢量积。这将返回相同的行，就好像在旧式的非 SQL-92 式联接中并没有指定 WHERE 子句。
WHERE
指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。基于 WHERE 子句中所指定的条件，有两种形式的删除操作。
搜索删除指定搜索条件限定删除的行。
定位删除使用 CURRENT OF 子句指定游标。删除操作在游标的当前位置发生。这比使用 WHERE search_condition 子句限定删除的行的搜索 DELETE 精确。如果搜索条件不唯一标识单行，则搜索 DELETE 删除多行。
<search_condition>
指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。有关更多信息，请参见搜索条件。
CURRENT OF
指定在指定游标的当前位置完成 DELETE。
GLOBAL
指定 cursor_name 指的是全局游标。
cursor_name
是从其中进行提取的打开游标的名称。当全局和局部游标都以 cursor_name 作为它们的名称存在时，如果指定 GLOBAL，则 cursor_name 引用全局游标，如果未指定 GLOBAL，则 cursor_name 引用局部游标。游标必须允许更新。
cursor_variable_name
是游标变量的名称。游标变量必须引用允许更新的游标。
OPTION (<query_hint> [,...n] )
是表示使用优化程序提示自定义 SQL Server 的语句处理的关键字。
{HASH | ORDER} GROUP
指定在查询的 GROUP BY 或 COMPUTE 子句中指定的聚合使用哈希或排列。
{MERGE | HASH | CONCAT} UNION
指定所有的 UNION 操作通过合并、哈希或串联 UNION 集合来完成。如果指定了不止一个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。
说明 如果还为 FROM 子句中的某对链接表指定了 <joint_hint>，则这个提示优先于 OPTION 子句中所指定的任何 <join_hint>。
FAST number_rows
指定对查询进行优化，以便快速检索第一个 number_rows（非负整数）。在第一个 number_rows 返回后，查询继续进行并生成完整的结果集。
FORCE ORDER
指定在查询优化过程中保持由查询语法表示的联接顺序。
MAXDOP number
只对指定了 sp_configure 的 max degree of parallelism 配置选项的查询替代该选项。当使用 MAXDOP 查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。有关更多信息，请参见 max degree of parallelism 选项。
ROBUST PLAN
强制查询优化器尝试执行一个计划，该计划以性能为代价获得最大可能的行大小。如果不能使用这样的计划，查询优化器将返回错误而不是延迟对查询执行的错误检测。行可以包含可变长度列；SQL Server 允许将行大小定义为超过 SQL Server 处理能力的最大可能的大小。通常，应用程序存储实际大小在 SQL Server 处理能力范围内的行，而不管最大可能大小。如果 SQL Server 遇到过长的行，则返回执行错误。
KEEP PLAN
强制查询优化器对查询放宽估计的重新编译阈值。当对表中索引列的更改（更新、删除或插入）达到估计数目时查询会自动重新编译，该估计数目即为重新编译阈值。指定 KEEP PLAN 将确保当表有多个更新时不会频繁地对查询进行重新编译。
注释
如果所修改的对象是 table 变量，则 DELETE 可用在用户定义函数的正文中。
对于由四部分组成的表名（或视图名称），若其中的服务器名称使用的是 OPENDATASOURCE 函数，则该表名可以在表名能够出现的任何位置作为表源使用。
如果 DELETE 语句违反了触发器，或试图删除另一个有 FOREIGN KEY 约束的表内的数据所引用的行，则可能会失败。如果 DELETE 删除了多行，而在删除的行中有任何一行违反触发器或约束，则将取消该语句，返回错误且不删除任何行。
如果在对表或视图的 DELETE 操作上定义了 INSTEAD-OF 触发器，该触发器将执行 instead of DELETE 语句。SQL Server 的早期版本只支持 DELETE 上的 AFTER 触发器和其它修改语句。
当 DELETE 语句遇到在表达式评估过程中发生的算术错误（溢出、被零除或域错误）时，SQL Server 将处理这些错误，就好象 SET ARITHABORT 打开一样。将取消批处理中的其余部分并返回错误信息。
对远程表和本地及远程分区视图上的 DELETE 语句将忽略 SET ROWCOUNT 选项的设置。
如果要删除在表中的所有行，则 TRUNCATE TABLE 比 DELETE 快。DELETE 以物理方式一次删除一行，并在事务日志中记录每个删除的行。TRUNCATE TABLE 则释放所有与表关联的页。因此，TRUNCATE TABLE 比 DELETE 快且需要的事务日志空间更少。TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 相当，但是 TRUNCATE TABLE 不能用于由外键引用的表。DELETE 和 TRUNCATE TABLE 都使删除的行所占用的空间可用于存储新数据。
权限
默认情况下，将 DELETE 权限授予 sysadmin 固定服务器角色成员、db_owner 和 db_datawriter 固定数据库角色成员以及表所有者。sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户。
如果语句包含 WHERE 子句，则还必须有 SELECT 权限。
示例
A. 不带参数使用 DELETE
下例从 authors表中删除所有行。
USE pubs
DELETE authors
B. 在行集上使用 DELETE
因为 au_lname 可能不是唯一的，下例删除其中的 au_lname 是 McBadden 的所有行。
USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
C. 在游标的当前行上使用 DELETE
下例显示在名为 complex_join_cursor 的游标上所做的删除。它只影响当前从游标提取的单行。
USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor
D. 基于子查询使用 DELETE 或使用 Transact-SQL 扩展
下例显示基于联接或相关子查询从基表中删除记录的 Transact-SQL 扩展。第一个 DELETE 显示与 SQL-92 兼容的子查询解决方法，第二个 DELETE 显示 Transact-SQL 扩展。两个查询都基于存储在 titles 表中的标题从 titleauthors 表中删除行。
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
E. 在 DELETE 和 SELECT 中使用 TOP 子句
由于可以在 DELETE 语句中指定 SELECT 语句，因此还可以在 SELECT 语句中使用 TOP 子句。例如，下例从 authors 表中删除前 10 个作者。
DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
从表中删除行。
语法
DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE
{ < search_condition >
| { [ 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
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
参数
FROM
是可选的关键字，可用在 DELETE 关键字与目标 table_name、view_name 或 rowset_function_limited 之间。
table_name
是要从其中删除行的表的名称。
在其作用域内的 table 变量、或是将 OPENDATASOURCE 函数作为服务器名称的由四部分组成的表名（或视图名称）还可以在 DELETE 语句中作为表源使用。
WITH (<table_hint_limited> [...n])
指定目标表所允许的一个或多个表提示。需要有 WITH 关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。有关表提示的更多信息，请参见 FROM。
view_name
是视图名称。在视图的 FROM 子句中，view_name 引用的视图必须可更新且正确引用一个基表。有关可更新视图的更多信息，请参见 CREATE VIEW。
说明 如果表或视图存在于另一个数据库内或有一个不同于当前用户的所有者，则使用格式为 server_name.database.[owner].object_name 的由四部分组成的合法名称。有关更多信息，请参见 Transact-SQL 语法规则。
rowset_function_limited
OPENQUERY 或 OPENROWSET 函数，视提供程序功能而定。有关提供程序所需功能的更多信息，请参见 OLE DB 提供程序的 UPDATE 和 DELETE 语句要求。有关行集函数的更多信息，请参见 OPENQUERY 和 OPENROWSET。
FROM < table_source >
指定附加的 FROM 子句。这个对 DELETE 的 Transact-SQL 扩展使您得以从 <table_sources> 指定数据，并从第一个 FROM 子句内的表中删除相应的行。
这个扩展指定联接，可在 WHERE 子句中取代子查询来标识要删除的行。
table_name [[AS] table_alias ]
是为删除操作提供标准值的表名。
view_name [ [ AS ] table_alias ]
是为删除操作提供标准值的视图名称。带 INSTEAD OF UPDATE 触发器的视图不能是含有 FROM 子句的 UPDATE 的目标。
WITH (<table_hint>
指定一个或更多表提示。有关表提示的更多信息，请参见 FROM。
rowset_function [ [AS] table_alias ]
是行集函数名和可选别名。有关行集函数列表的更多信息，请参见行集函数。
derived_table [AS] table_alias
是从数据库中检索行的子查询。derived_table 用作对外部查询的输入。
column_alias
替换结果集内列名的可选别名。在选择列表中放入每个列的一个别名，并将整个列别名列表用圆括号括起来。
<joined_table>
由两个或更多表的积组成的结果集，例如：
SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
对于多个 CROSS 联接，请使用圆括号来更改联接的自然顺序。
<join_type>
指定联接操作的类型。
INNER
指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。
LEFT [OUTER]
指定在结果集内包含左表中所有不满足指定条件的行，并将右表中的输出列设置为 NULL 以作为对内联接所返回的所有行的补充。
RIGHT [OUTER]
指定在结果集内包含右表中所有不满足指定条件的行，并将左表中的输出列设置为 NULL 以作为对内联接所返回的所有行的补充。
FULL [OUTER]
如果来自左表或右表的某行与选择准则不匹配，则指定在结果集内包含该行，并且将与另一个表对应的输出列设置为 NULL。除此之外，结果集中还包含通常由内联接返回的所有行。
JOIN
是表示在删除操作中使用 SQL-92 式联接的关键字。
ON <search_condition>
指定联接所基于的条件。尽管经常使用列和比较运算符，但此条件可指定任何谓词，例如：
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
当条件指定列时，列不必具有相同的名称或数据类型；但是，如果数据类型不一致，则这些列必须相互兼容或是 Microsoft® SQL Server™ 能够隐性转换的类型。如果数据类型不能隐性转换，则条件必须使用 CAST 函数显式转换数据类型。
有关搜索条件和谓词的更多信息，请参见搜索条件。
CROSS JOIN
指定两个表的矢量积。这将返回相同的行，就好像在旧式的非 SQL-92 式联接中并没有指定 WHERE 子句。
WHERE
指定用于限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。基于 WHERE 子句中所指定的条件，有两种形式的删除操作。
搜索删除指定搜索条件限定删除的行。
定位删除使用 CURRENT OF 子句指定游标。删除操作在游标的当前位置发生。这比使用 WHERE search_condition 子句限定删除的行的搜索 DELETE 精确。如果搜索条件不唯一标识单行，则搜索 DELETE 删除多行。
<search_condition>
指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。有关更多信息，请参见搜索条件。
CURRENT OF
指定在指定游标的当前位置完成 DELETE。
GLOBAL
指定 cursor_name 指的是全局游标。
cursor_name
是从其中进行提取的打开游标的名称。当全局和局部游标都以 cursor_name 作为它们的名称存在时，如果指定 GLOBAL，则 cursor_name 引用全局游标，如果未指定 GLOBAL，则 cursor_name 引用局部游标。游标必须允许更新。
cursor_variable_name
是游标变量的名称。游标变量必须引用允许更新的游标。
OPTION (<query_hint> [,...n] )
是表示使用优化程序提示自定义 SQL Server 的语句处理的关键字。
{HASH | ORDER} GROUP
指定在查询的 GROUP BY 或 COMPUTE 子句中指定的聚合使用哈希或排列。
{MERGE | HASH | CONCAT} UNION
指定所有的 UNION 操作通过合并、哈希或串联 UNION 集合来完成。如果指定了不止一个 UNION 提示，查询优化器就会从这些指定的提示中选择开销最少的策略。
说明 如果还为 FROM 子句中的某对链接表指定了 <joint_hint>，则这个提示优先于 OPTION 子句中所指定的任何 <join_hint>。
FAST number_rows
指定对查询进行优化，以便快速检索第一个 number_rows（非负整数）。在第一个 number_rows 返回后，查询继续进行并生成完整的结果集。
FORCE ORDER
指定在查询优化过程中保持由查询语法表示的联接顺序。
MAXDOP number
只对指定了 sp_configure 的 max degree of parallelism 配置选项的查询替代该选项。当使用 MAXDOP 查询提示时，所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。有关更多信息，请参见 max degree of parallelism 选项。
ROBUST PLAN
强制查询优化器尝试执行一个计划，该计划以性能为代价获得最大可能的行大小。如果不能使用这样的计划，查询优化器将返回错误而不是延迟对查询执行的错误检测。行可以包含可变长度列；SQL Server 允许将行大小定义为超过 SQL Server 处理能力的最大可能的大小。通常，应用程序存储实际大小在 SQL Server 处理能力范围内的行，而不管最大可能大小。如果 SQL Server 遇到过长的行，则返回执行错误。
KEEP PLAN
强制查询优化器对查询放宽估计的重新编译阈值。当对表中索引列的更改（更新、删除或插入）达到估计数目时查询会自动重新编译，该估计数目即为重新编译阈值。指定 KEEP PLAN 将确保当表有多个更新时不会频繁地对查询进行重新编译。
注释
如果所修改的对象是 table 变量，则 DELETE 可用在用户定义函数的正文中。
对于由四部分组成的表名（或视图名称），若其中的服务器名称使用的是 OPENDATASOURCE 函数，则该表名可以在表名能够出现的任何位置作为表源使用。
如果 DELETE 语句违反了触发器，或试图删除另一个有 FOREIGN KEY 约束的表内的数据所引用的行，则可能会失败。如果 DELETE 删除了多行，而在删除的行中有任何一行违反触发器或约束，则将取消该语句，返回错误且不删除任何行。
如果在对表或视图的 DELETE 操作上定义了 INSTEAD-OF 触发器，该触发器将执行 instead of DELETE 语句。SQL Server 的早期版本只支持 DELETE 上的 AFTER 触发器和其它修改语句。
当 DELETE 语句遇到在表达式评估过程中发生的算术错误（溢出、被零除或域错误）时，SQL Server 将处理这些错误，就好象 SET ARITHABORT 打开一样。将取消批处理中的其余部分并返回错误信息。
对远程表和本地及远程分区视图上的 DELETE 语句将忽略 SET ROWCOUNT 选项的设置。
如果要删除在表中的所有行，则 TRUNCATE TABLE 比 DELETE 快。DELETE 以物理方式一次删除一行，并在事务日志中记录每个删除的行。TRUNCATE TABLE 则释放所有与表关联的页。因此，TRUNCATE TABLE 比 DELETE 快且需要的事务日志空间更少。TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 相当，但是 TRUNCATE TABLE 不能用于由外键引用的表。DELETE 和 TRUNCATE TABLE 都使删除的行所占用的空间可用于存储新数据。
权限
默认情况下，将 DELETE 权限授予 sysadmin 固定服务器角色成员、db_owner 和 db_datawriter 固定数据库角色成员以及表所有者。sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户。
如果语句包含 WHERE 子句，则还必须有 SELECT 权限。
示例
A. 不带参数使用 DELETE
下例从 authors表中删除所有行。
USE pubs
DELETE authors
B. 在行集上使用 DELETE
因为 au_lname 可能不是唯一的，下例删除其中的 au_lname 是 McBadden 的所有行。
USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
C. 在游标的当前行上使用 DELETE
下例显示在名为 complex_join_cursor 的游标上所做的删除。它只影响当前从游标提取的单行。
USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor
D. 基于子查询使用 DELETE 或使用 Transact-SQL 扩展
下例显示基于联接或相关子查询从基表中删除记录的 Transact-SQL 扩展。第一个 DELETE 显示与 SQL-92 兼容的子查询解决方法，第二个 DELETE 显示 Transact-SQL 扩展。两个查询都基于存储在 titles 表中的标题从 titleauthors 表中删除行。
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
E. 在 DELETE 和 SELECT 中使用 TOP 子句
由于可以在 DELETE 语句中指定 SELECT 语句，因此还可以在 SELECT 语句中使用 TOP 子句。例如，下例从 authors 表中删除前 10 个作者。
DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
hzjf 2001-04-29
- 打赏
- 举报
如果你觉得Ｅ文看不懂的话，这里有一个简单一点的Ｚ文的。
删除记录
要从表中删除一个或多个记录，需要使用SQL DELETE语句。你可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于’Delete Me’的记录：
DELETE mytable WHERE first_column=’Deltet Me’
DELETE 语句的完整句法如下：
DELETE [FROM] {table_name|view_name} [WHERE clause]
在SQL SELECT 语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句 中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为’goodbye’或second_column字段的值为’so long’的记录：
DELETE mytable WHERE first_column=’goodby’ OR second_column=’so long’
如果你不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的TRUNCATE TABLE语句。
注意：
为什么要用TRUNCATE TABLE 语句代替DELETE语句？当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE 要比DELETE快得多。
更新记录
要修改表中已经存在的一条或多条记录，应使用SQL UPDATE语句。同DELETE语句一样，UPDATE语句可以使用WHERE子句来选择更新特定的记录。请看这个例子：
UPDATE mytable SET first_column=’Updated!’ WHERE second_column=’Update Me!’
这个UPDATE 语句更新所有second_column字段的值为’Update Me!’的记录。对所有被选中的记录，字段first_column的值被置为’Updated!’。
下面是UPDATE语句的完整句法：
UPDATE {table_name|view_name} SET [{table_name|view_name}]
{column_list|variable_list|variable_and_column_list}
[,{column_list2|variable_list2|variable_and_column_list2}…
[,{column_listN|variable_listN|variable_and_column_listN}]]
[WHERE clause]
注意：
你可以对文本型字段使用UPDATE语句。但是，如果你需要更新很长的字符串，应使用UPDATETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。
如果你不提供WHERE子句，表中的所有记录都将被更新。有时这是有用的。例如，如果你想把表titles中的所有书的价格加倍，你可以使用如下的UPDATE 语句：
你也可以同时更新多个字段。例如，下面的UPDATE语句同时更新first_column,second_column,和third_column这三个字段：
UPDATE mytable SET first_column=’Updated!’
Second_column=’Updated!’
Third_column=’Updated!’
WHERE first_column=’Update Me1’
技巧：
SQL忽略语句中多余的空格。你可以把SQL语句写成任何你最容易读的格式。
删除记录
要从表中删除一个或多个记录，需要使用SQL DELETE语句。你可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于’Delete Me’的记录：
DELETE mytable WHERE first_column=’Deltet Me’
DELETE 语句的完整句法如下：
DELETE [FROM] {table_name|view_name} [WHERE clause]
在SQL SELECT 语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句 中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为’goodbye’或second_column字段的值为’so long’的记录：
DELETE mytable WHERE first_column=’goodby’ OR second_column=’so long’
如果你不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的TRUNCATE TABLE语句。
注意：
为什么要用TRUNCATE TABLE 语句代替DELETE语句？当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE 要比DELETE快得多。
更新记录
要修改表中已经存在的一条或多条记录，应使用SQL UPDATE语句。同DELETE语句一样，UPDATE语句可以使用WHERE子句来选择更新特定的记录。请看这个例子：
UPDATE mytable SET first_column=’Updated!’ WHERE second_column=’Update Me!’
这个UPDATE 语句更新所有second_column字段的值为’Update Me!’的记录。对所有被选中的记录，字段first_column的值被置为’Updated!’。
下面是UPDATE语句的完整句法：
UPDATE {table_name|view_name} SET [{table_name|view_name}]
{column_list|variable_list|variable_and_column_list}
[,{column_list2|variable_list2|variable_and_column_list2}…
[,{column_listN|variable_listN|variable_and_column_listN}]]
[WHERE clause]
注意：
你可以对文本型字段使用UPDATE语句。但是，如果你需要更新很长的字符串，应使用UPDATETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。
如果你不提供WHERE子句，表中的所有记录都将被更新。有时这是有用的。例如，如果你想把表titles中的所有书的价格加倍，你可以使用如下的UPDATE 语句：
你也可以同时更新多个字段。例如，下面的UPDATE语句同时更新first_column,second_column,和third_column这三个字段：
UPDATE mytable SET first_column=’Updated!’
Second_column=’Updated!’
Third_column=’Updated!’
WHERE first_column=’Update Me1’
技巧：
SQL忽略语句中多余的空格。你可以把SQL语句写成任何你最容易读的格式。
ePing 2001-04-29
- 打赏
- 举报
【Update】
Changes existing data in a table.7
Syntax
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> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}
<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
}
Arguments
table_name
Is the name of the table to update. The name can be qualified with the linked server, database, and owner name if the table is not in the current server or database, or is not owned by the current user.
WITH (<table_hint_limited> [...n])
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, READUNCOMMITTED, and UPDLOCK are not allowed. For information about table hints, see FROM.
view_name
Is the name of the view to update. The view referenced by view_name must be updatable. The modifications made by the UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view. For more information on updatable views, see CREATE VIEW.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and OPENROWSET.
SET
Specifies the list of column or variable names to be updated.
column_name
Is a column that contains the data to be changed. column_name must reside in the table or view specified in the UPDATE clause. Identity columns cannot be updated.
If a qualified column name is specified, the qualifier must match the table or view name in the UPDATE clause. For example, this is valid:
UPDATE authors
SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
A table alias specified in a FROM clause cannot be used as a qualifier in SET
column_name. For example, this is not valid:
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
To make the example work, remove the t. alias from the column name.
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
expression
Is a variable, literal value, expression, or a parenthesized subSELECT statement that returns a single value. The value returned by expression replaces the existing value in column_name or @variable.
DEFAULT
Specifies that the default value defined for the column is to replace the existing value in the column. This can also be used to change the column to NULL if the column has no default and is defined to allow null values.
@variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
FROM <table_source>
Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.
table_name [[AS] table_alias ]
Is the name of a table to provide criteria for the update operation.
If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias
may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias.
view_name [ [AS] table_alias ]
Is the name of a view to provide criteria for the update operation.
WITH (<table_hint> [...n])
Specifies one or more table hints for a source table. For information about table hints, see FROM.
rowset_function [ [AS] table_alias ]
Is the name of any rowset function and an optional alias. For information about a list of rowset functions, see Rowset Functions.
derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
<joined_table>
Is a result set that is the product of two or more tables, for example:
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
<join_type>
Specifies the type of join operation.
INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [OUTER]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [OUTER]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
<join_hint>
Specifies a join hint or execution algorithm. If <join_hint> is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified. For more information about joint hints, see FROM.
JOIN
Indicates that the specified tables or views should be joined.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft?SQL Server?can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified.
WHERE
Specifies the conditions that limit the rows that are updated. 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.
?Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor.
<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. For more information about predicates and search conditions, see Search Condition.
CURRENT OF
Specifies that the update is performed at the current position of the specified cursor.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name refers to the global cursor if GLOBAL is specified. If GLOBAL is not specified,
cursor_name refers to the local cursor. The cursor must allow updates.
cursor_variable_name
Is the name of a cursor variable. cursor_variable_name must reference a cursor that allows updates.
OPTION (<query_hint> [,...n] )
Specifies that optimizer hints are used to customize SQL Server抯 processing of the statement.
{HASH | ORDER} GROUP
Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.
{LOOP | MERGE | HASH |} JOIN
Specifies that all join operations are performed by loop join, merge join, or hash join in the whole query. If more than one join hint is specified, the query optimizer selects the least expensive join strategy for the allowed ones. If, in the same query, a join hint is also specified for a specific pair of tables, it takes precedence in the joining of the two tables.
{MERGE | HASH | CONCAT} UNION
Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
Note If a join hint is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any join hint specified in the OPTION clause.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax should be preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If no such plan is possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined whose maximum potential size is beyond the ability of SQL Server to process. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.
Remarks
If an update to a row violates a constraint or rule, if it violates the NULL setting for the column, or if the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.
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 remainder of the batch is not executed, and an error message is returned.
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.
If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.
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 updating the column with NULL.
Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.
All char and nchar columns are right-padded to the defined length.
If ANSI_PADDING is set OFF, all trailing spaces are removed from data inserted into varchar and nvarchar columns, except in strings containing only spaces. These strings are truncated to an empty string. If ANSI_PADDING is set ON, trailing spaces are inserted. The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. This can be configured in ODBC data sources or by setting connection attributes or properties.
A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. 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.
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 (in other words, if the UPDATE statement is not deterministic). For example, given the UPDATE statement in the following script, both rows in table s meet the qualifications of the FROM clause in the UPDATE statement, but it is undefined which row from s is used to update the row in table t.
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
The same problem can occur when combining the FROM and WHERE CURRENT OF clauses. In this example, both rows in table t2 meet the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from t2 is to be used to update the row in table t1.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go
DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1
OPEN abc
FETCH abc
UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
GO
Setting Variables and Columns
Variable names can be used in UPDATE statements to show the old and new values affected. This should only be used when the UPDATE statement affects a single record; if the UPDATE statement affects multiple records, the variables only contain the values for one of the updated rows.
Permissions
UPDATE permissions default to members of the sysadmin fixed server role, the
db_owner fixed database role, and the table owner, who can transfer permissions to other users. 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.
Examples
A. Use a simple UPDATE
These examples show how all rows can be affected if a WHERE clause is eliminated from an UPDATE statement.
If all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this example shows how the publishers table can updated.
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
This example changes the names of all the publishers to NULL.
UPDATE publishers
SET pub_name = NULL
You can also use computed values in an update. This example doubles all prices in the titles table.
UPDATE titles
SET price = price * 2
B. Use the UPDATE statement with a WHERE clause
The WHERE clause specifies the rows to update. For example, in the unlikely event that northern California is renamed Pacifica (abbreviated PC) and the people of Oakland vote to change the name of their city to Bay City. This example shows how to update the authors table for all former Oakland residents whose addresses are now out of date.
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
You must write another statement to change the name of the state for residents of other northern California cities.
C. Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
This example assumes that only one set of sales is recorded for a given title on a given date and that updates are current. If this is not the case (if more than one sale for a given title can be recorded on the same day), the example shown here does not work correctly. It executes without error, but each title is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row twice.
In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in this example:
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. Use UPDATE with the TOP clause in a SELECT statement
This example updates the state column for the first 10 authors from the authors
table.
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
【Delete】
emoves rows from a table.
Syntax
DELETE
[FROM ]
{
table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited
}
[ FROM {<table_source>} [,...n] ]
[WHERE
{ <search_condition>
| { [ 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> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}
<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
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
Arguments
FROM
Is an optional keyword that can be used between the DELETE keyword and the target table_name, view_name, or rowset_function_limited.
table_name
Is the name of the table from which the rows are to be removed.
WITH (<table_hint_limited> [...n])
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, READUNCOMMITTED, and UPDLOCK are not allowed. For information about table hints, see FROM.
view_name
Is the name of a view. The view referenced by view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information on updatable views, see CREATE VIEW.
Note If the table or view exists in another database or has an owner other than the current user, use a four-part qualified name in the format server_name
.database.[owner].object_name. For more information, see Transact-SQL Syntax Conventions.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and
OPENROWSET.
FROM <table_source>
Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows you to specify data from <table_sources> and delete corresponding rows from the table in the first FROM clause.
This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed. For more information, see FROM.
table_name [[AS] table_alias ]
Is the name of the table to provide criteria values for the delete operation.
view_name [ [AS] table_alias ]
Is the name of the view to provide criteria values for the delete operation.
WITH (<table_hint>
Specifies one or more table hints. For more information about table hints, see FROM.
rowset_function [ [AS] table_alias ]
Is the name of a rowset function and an optional alias. For more information about a list of rowset functions, see Rowset Functions.
derived_table [AS] table_alias
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
<joined_table>
Is a result set that is the product of two or more tables, for example:
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
<join_type>
Specifies the type of join operation.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join.
RIGHT [OUTER]
Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join.
FULL [OUTER]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
JOIN
Is a keyword to indicate that an SQL-92 style join be used in the delete operation.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, they need not have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft?SQL Server?can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.
WHERE
Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table. There are two forms of delete operations based on what is specified in the WHERE clause:
?Searched deletes specify a search condition to qualify the rows to delete.
?Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE deletes multiple rows if the search condition does not uniquely identify a single row.
<search_condition>
Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition. For more information, see Search Condition.
CURRENT OF
Specifies that the DELETE is done at the current position of the specified cursor.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch is made. 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, and to the local cursor otherwise. The cursor must allow updates.
cursor_variable_name
Is the name of a cursor variable. The cursor variable must reference a cursor that allows updates.
OPTION (<query_hint> [,...n] )
Are keywords indicating that optimizer hints are used to customize SQL Server抯 processing of the statement.
{HASH | ORDER} GROUP
Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.
{MERGE | HASH | CONCAT} UNION
Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
Note If a <joint_hint> is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any <join_hint> specified in the OPTION clause.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If such a plan is not possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined that have a maximum potential size beyond the ability of SQL Server to process them. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.
Remarks
DELETE cannot remove data from tables on the nullable side of an outer join.
The DELETE statement may fail if it violates a trigger or attempts to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, SQL Server handles these errors as if SET ARITHABORT is ON. The remainder of the batch is canceled, and an error message is returned.
If you want to delete all the rows in a table, TRUNCATE TABLE is faster than DELETE. DELETE physically removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE deallocates all pages associated with the table. For this reason, TRUNCATE TABLE is faster and requires less transaction log space than DELETE. TRUNCATE TABLE is functionally equivalent to DELETE with no WHERE clause, but TRUNCATE TABLE cannot be used with tables referenced by foreign keys. Both DELETE and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.
Permissions
DELETE permissions default to members of the sysadmin fixed server role, members of the db_owner fixed database role, and the table owner, who can transfer permissions to other users. SELECT permissions are also required if the statement contains a WHERE clause.
Examples
A. Use DELETE with no parameters
This example deletes all rows from the authors table.
USE pubs
DELETE authors
B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.
USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
C. Use DELETE on the current row of a cursor
This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched from the cursor.
USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor
D. Use DELETE based on a subquery or use the Transact-SQL extension
This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
E. Use DELETE and a SELECT with the TOP Clause
Because a SELECT statement can be specified in a DELETE statement, the TOP clause can also be used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.
DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
Changes existing data in a table.7
Syntax
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> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}
<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
}
Arguments
table_name
Is the name of the table to update. The name can be qualified with the linked server, database, and owner name if the table is not in the current server or database, or is not owned by the current user.
WITH (<table_hint_limited> [...n])
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, READUNCOMMITTED, and UPDLOCK are not allowed. For information about table hints, see FROM.
view_name
Is the name of the view to update. The view referenced by view_name must be updatable. The modifications made by the UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view. For more information on updatable views, see CREATE VIEW.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and OPENROWSET.
SET
Specifies the list of column or variable names to be updated.
column_name
Is a column that contains the data to be changed. column_name must reside in the table or view specified in the UPDATE clause. Identity columns cannot be updated.
If a qualified column name is specified, the qualifier must match the table or view name in the UPDATE clause. For example, this is valid:
UPDATE authors
SET authors.au_fname = 'Annie'
WHERE au_fname = 'Anne'
A table alias specified in a FROM clause cannot be used as a qualifier in SET
column_name. For example, this is not valid:
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
To make the example work, remove the t. alias from the column name.
UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
expression
Is a variable, literal value, expression, or a parenthesized subSELECT statement that returns a single value. The value returned by expression replaces the existing value in column_name or @variable.
DEFAULT
Specifies that the default value defined for the column is to replace the existing value in the column. This can also be used to change the column to NULL if the column has no default and is defined to allow null values.
@variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
FROM <table_source>
Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.
table_name [[AS] table_alias ]
Is the name of a table to provide criteria for the update operation.
If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias
may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias.
view_name [ [AS] table_alias ]
Is the name of a view to provide criteria for the update operation.
WITH (<table_hint> [...n])
Specifies one or more table hints for a source table. For information about table hints, see FROM.
rowset_function [ [AS] table_alias ]
Is the name of any rowset function and an optional alias. For information about a list of rowset functions, see Rowset Functions.
derived_table
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
<joined_table>
Is a result set that is the product of two or more tables, for example:
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
<join_type>
Specifies the type of join operation.
INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [OUTER]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [OUTER]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
<join_hint>
Specifies a join hint or execution algorithm. If <join_hint> is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified. For more information about joint hints, see FROM.
JOIN
Indicates that the specified tables or views should be joined.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft?SQL Server?can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified.
WHERE
Specifies the conditions that limit the rows that are updated. 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.
?Positioned updates use the CURRENT OF clause to specify a cursor. The update operation occurs at the current position of the cursor.
<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. For more information about predicates and search conditions, see Search Condition.
CURRENT OF
Specifies that the update is performed at the current position of the specified cursor.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name refers to the global cursor if GLOBAL is specified. If GLOBAL is not specified,
cursor_name refers to the local cursor. The cursor must allow updates.
cursor_variable_name
Is the name of a cursor variable. cursor_variable_name must reference a cursor that allows updates.
OPTION (<query_hint> [,...n] )
Specifies that optimizer hints are used to customize SQL Server抯 processing of the statement.
{HASH | ORDER} GROUP
Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.
{LOOP | MERGE | HASH |} JOIN
Specifies that all join operations are performed by loop join, merge join, or hash join in the whole query. If more than one join hint is specified, the query optimizer selects the least expensive join strategy for the allowed ones. If, in the same query, a join hint is also specified for a specific pair of tables, it takes precedence in the joining of the two tables.
{MERGE | HASH | CONCAT} UNION
Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
Note If a join hint is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any join hint specified in the OPTION clause.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax should be preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If no such plan is possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined whose maximum potential size is beyond the ability of SQL Server to process. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.
Remarks
If an update to a row violates a constraint or rule, if it violates the NULL setting for the column, or if the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.
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 remainder of the batch is not executed, and an error message is returned.
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.
If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.
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 updating the column with NULL.
Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.
All char and nchar columns are right-padded to the defined length.
If ANSI_PADDING is set OFF, all trailing spaces are removed from data inserted into varchar and nvarchar columns, except in strings containing only spaces. These strings are truncated to an empty string. If ANSI_PADDING is set ON, trailing spaces are inserted. The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. This can be configured in ODBC data sources or by setting connection attributes or properties.
A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. 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.
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 (in other words, if the UPDATE statement is not deterministic). For example, given the UPDATE statement in the following script, both rows in table s meet the qualifications of the FROM clause in the UPDATE statement, but it is undefined which row from s is used to update the row in table t.
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
The same problem can occur when combining the FROM and WHERE CURRENT OF clauses. In this example, both rows in table t2 meet the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from t2 is to be used to update the row in table t1.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go
DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1
OPEN abc
FETCH abc
UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
GO
Setting Variables and Columns
Variable names can be used in UPDATE statements to show the old and new values affected. This should only be used when the UPDATE statement affects a single record; if the UPDATE statement affects multiple records, the variables only contain the values for one of the updated rows.
Permissions
UPDATE permissions default to members of the sysadmin fixed server role, the
db_owner fixed database role, and the table owner, who can transfer permissions to other users. 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.
Examples
A. Use a simple UPDATE
These examples show how all rows can be affected if a WHERE clause is eliminated from an UPDATE statement.
If all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this example shows how the publishers table can updated.
UPDATE publishers
SET city = 'Atlanta', state = 'GA'
This example changes the names of all the publishers to NULL.
UPDATE publishers
SET pub_name = NULL
You can also use computed values in an update. This example doubles all prices in the titles table.
UPDATE titles
SET price = price * 2
B. Use the UPDATE statement with a WHERE clause
The WHERE clause specifies the rows to update. For example, in the unlikely event that northern California is renamed Pacifica (abbreviated PC) and the people of Oakland vote to change the name of their city to Bay City. This example shows how to update the authors table for all former Oakland residents whose addresses are now out of date.
UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
You must write another statement to change the name of the state for residents of other northern California cities.
C. Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
This example assumes that only one set of sales is recorded for a given title on a given date and that updates are current. If this is not the case (if more than one sale for a given title can be recorded on the same day), the example shown here does not work correctly. It executes without error, but each title is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row twice.
In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in this example:
UPDATE titles
SET ytd_sales =
(SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id
AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
FROM titles, sales
D. Use UPDATE with the TOP clause in a SELECT statement
This example updates the state column for the first 10 authors from the authors
table.
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id
【Delete】
emoves rows from a table.
Syntax
DELETE
[FROM ]
{
table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited
}
[ FROM {<table_source>} [,...n] ]
[WHERE
{ <search_condition>
| { [ 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> ::=
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
}
<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
| FAST number_rows
| FORCE ORDER
| MAXDOP
| ROBUST PLAN
| KEEP PLAN
}
Arguments
FROM
Is an optional keyword that can be used between the DELETE keyword and the target table_name, view_name, or rowset_function_limited.
table_name
Is the name of the table from which the rows are to be removed.
WITH (<table_hint_limited> [...n])
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, READUNCOMMITTED, and UPDLOCK are not allowed. For information about table hints, see FROM.
view_name
Is the name of a view. The view referenced by view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information on updatable views, see CREATE VIEW.
Note If the table or view exists in another database or has an owner other than the current user, use a four-part qualified name in the format server_name
.database.[owner].object_name. For more information, see Transact-SQL Syntax Conventions.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and
OPENROWSET.
FROM <table_source>
Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows you to specify data from <table_sources> and delete corresponding rows from the table in the first FROM clause.
This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed. For more information, see FROM.
table_name [[AS] table_alias ]
Is the name of the table to provide criteria values for the delete operation.
view_name [ [AS] table_alias ]
Is the name of the view to provide criteria values for the delete operation.
WITH (<table_hint>
Specifies one or more table hints. For more information about table hints, see FROM.
rowset_function [ [AS] table_alias ]
Is the name of a rowset function and an optional alias. For more information about a list of rowset functions, see Rowset Functions.
derived_table [AS] table_alias
Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.
column_alias
Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.
<joined_table>
Is a result set that is the product of two or more tables, for example:
FROM tab1 LEFT OUTER JOIN tab1.c3 = tab2.c3
RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
ON tab3.c1
ON tab2.c3 = tab4.c3
For multiple CROSS joins, use parentheses to change the natural order of the joins.
<join_type>
Specifies the type of join operation.
INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]
Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join.
RIGHT [OUTER]
Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join.
FULL [OUTER]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
JOIN
Is a keyword to indicate that an SQL-92 style join be used in the delete operation.
ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
When the condition specifies columns, they need not have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft?SQL Server?can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.
For more information about search conditions and predicates, see Search Condition.
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.
WHERE
Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table. There are two forms of delete operations based on what is specified in the WHERE clause:
?Searched deletes specify a search condition to qualify the rows to delete.
?Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE deletes multiple rows if the search condition does not uniquely identify a single row.
<search_condition>
Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition. For more information, see Search Condition.
CURRENT OF
Specifies that the DELETE is done at the current position of the specified cursor.
GLOBAL
Specifies that cursor_name refers to a global cursor.
cursor_name
Is the name of the open cursor from which the fetch is made. 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, and to the local cursor otherwise. The cursor must allow updates.
cursor_variable_name
Is the name of a cursor variable. The cursor variable must reference a cursor that allows updates.
OPTION (<query_hint> [,...n] )
Are keywords indicating that optimizer hints are used to customize SQL Server抯 processing of the statement.
{HASH | ORDER} GROUP
Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.
{MERGE | HASH | CONCAT} UNION
Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.
Note If a <joint_hint> is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any <join_hint> specified in the OPTION clause.
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization.
MAXDOP number
Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.
ROBUST PLAN
Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If such a plan is not possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined that have a maximum potential size beyond the ability of SQL Server to process them. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.
KEEP PLAN
Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.
Remarks
DELETE cannot remove data from tables on the nullable side of an outer join.
The DELETE statement may fail if it violates a trigger or attempts to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, SQL Server handles these errors as if SET ARITHABORT is ON. The remainder of the batch is canceled, and an error message is returned.
If you want to delete all the rows in a table, TRUNCATE TABLE is faster than DELETE. DELETE physically removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE deallocates all pages associated with the table. For this reason, TRUNCATE TABLE is faster and requires less transaction log space than DELETE. TRUNCATE TABLE is functionally equivalent to DELETE with no WHERE clause, but TRUNCATE TABLE cannot be used with tables referenced by foreign keys. Both DELETE and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.
Permissions
DELETE permissions default to members of the sysadmin fixed server role, members of the db_owner fixed database role, and the table owner, who can transfer permissions to other users. SELECT permissions are also required if the statement contains a WHERE clause.
Examples
A. Use DELETE with no parameters
This example deletes all rows from the authors table.
USE pubs
DELETE authors
B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.
USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
C. Use DELETE on the current row of a cursor
This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched from the cursor.
USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor
D. Use DELETE based on a subquery or use the Transact-SQL extension
This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.
/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
E. Use DELETE and a SELECT with the TOP Clause
Because a SELECT statement can be specified in a DELETE statement, the TOP clause can also be used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.
DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
