SQL 如何更新sql server中的前100条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1198364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how can I Update top 100 records in sql server
提问by Rajesh
I want to update the top 100 records in SQL Server. I have a table T1with fields F1and F2. T1has 200 records. I want to update the F1field in the top 100 records. How can I update based on TOP 100in SQL Server?
我想更新 SQL Server 中的前 100 条记录。我有一个T1包含字段F1和F2. T1有 200 条记录。我想更新F1前 100 条记录中的字段。如何基于TOP 100SQL Server 进行更新？
回答by Umair Ahmed
Note, the parentheses are required for UPDATE statements:
请注意，UPDATE 语句需要括号：
update top (100) table1 set field1 = 1
回答by Martin Smith
Without an ORDER BYthe whole idea of TOPdoesn't make much sense. You need to have a consistent definition of which direction is "up" and which is "down" for the concept of top to be meaningful.
没有一个ORDER BY完整的想法是TOP没有多大意义的。您需要对“向上”和“向下”的方向有一致的定义，这样顶部的概念才有意义。
Nonetheless SQL Server allows it but doesn't guarantee a deterministic result.
尽管如此，SQL Server 允许它但不保证确定性的结果。
The UPDATE TOPsyntax in the accepted answer does not support an ORDER BYclause but it is possible to get deterministic semantics here by using a CTE or derived table to define the desired sort order as below.
UPDATE TOP接受的答案中的语法不支持ORDER BY子句，但可以通过使用 CTE 或派生表来定义所需的排序顺序，在这里获得确定性语义，如下所示。
;WITH CTE AS
(
SELECT TOP 100 *
FROM T1
ORDER BY F2
)
UPDATE CTE SET F1='foo'
回答by Claudio B
for those like me still stuck with SQL Server 2000, SET ROWCOUNT {number};can be used before the UPDATEquery
对于像我一样仍然坚持使用 SQL Server 2000 的人，SET ROWCOUNT {number};可以在UPDATE查询之前使用
SET ROWCOUNT 100;
UPDATE Table SET ..;
SET ROWCOUNT 0;
will limit the update to 100 rows
将更新限制为 100 行
It has been deprecated at least since SQL 2005, but as of SQL 2017 it still works. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017
至少自 SQL 2005 以来它已被弃用，但从 SQL 2017 开始它仍然有效。 https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017
回答by hyyxing
update tb set f1=1 where id in (select top 100 id from tb where f1=0)
回答by Michael Goldshteyn
What's even cooler is the fact that you can use an inline Table-Valued Function to select which (and how many via TOP) row(s) to update. That is:
更酷的是，您可以使用内联表值函数来选择TOP要更新的行（以及通过的行数）。那是：
UPDATE MyTable
SET Column1=@Value1
FROM tvfSelectLatestRowOfMyTableMatchingCriteria(@Param1,@Param2,@Param3)
For the table valued function you have something interesting to select the row to update like:
对于表值函数，您可以选择要更新的行，例如：
CREATE FUNCTION tvfSelectLatestRowOfMyTableMatchingCriteria
(
@Param1 INT,
@Param2 INT,
@Param3 INT
)
RETURNS TABLE AS RETURN
(
SELECT TOP(1) MyTable.*
FROM MyTable
JOIN MyOtherTable
ON ...
JOIN WhoKnowsWhatElse
ON ...
WHERE MyTable.SomeColumn=@Param1 AND ...
ORDER BY MyTable.SomeDate DESC
)
..., and there lies (in my humble opinion) the true power of updating only top selected rows deterministically while at the same time simplifying the syntax of the UPDATEstatement.
...，并且（在我看来）确定性地仅更新顶部选定的行的真正力量在于同时简化UPDATE语句的语法。
回答by Shahin Al Kabir Mitul
Try:
尝试：
UPDATE Dispatch_Post
SET isSync = 1
WHERE ChallanNo
IN (SELECT TOP 1000 ChallanNo FROM dbo.Dispatch_Post ORDER BY
CreatedDate DESC)
回答by Vanderlei Pires
You can also update from select using alias and join:
您还可以使用别名和加入从选择更新：
UPDATE TOP (500) T
SET T.SomeColumn = 'Value'
FROM SomeTable T
INNER JOIN OtherTable O ON O.OtherTableFK = T.SomeTablePK
WHERE T.SomeOtherColumn = 1
