SQL Server 2000 TOP 和 OUTPUT 关键字 [英] SQL Server 2000 TOP and OUTPUT keywords

问题描述

此查询在 SQL Server 2005 和 2008 中运行良好.我将如何在 SQL Server 2000 中编写它?

This query works great in SQL Server 2005 and 2008. How would I write it in SQL Server 2000?

UPDATE TOP 10 myTable
SET myBooleanColumn = 1
OUTPUT inserted.*

除了运行多个查询之外，还有什么方法可以做到吗?

Is there any way to do it besides running multiple queries?

推荐答案

老实说，您的查询没有任何意义，我很难理解您对伟大"的标准.当然，它更新了 10 行，并且不会出错.但是你真的不在乎它更新了哪 10 行吗?您当前没有 ORDER BY 的 TOP 表明您希望 SQL Server 决定要更新哪些行(这正是它要做的).

To be honest, your query doesn't really make sense, and I have a hard time understanding your criteria for "great." Sure, it updates 10 rows, and doesn't give an error. But do you really not care which 10 rows it updates? Your current TOP without ORDER BY suggests that you want SQL Server to decide which rows to update (and that's exactly what it will do).

要在 SQL Server 2000 中完成此操作(不使用触发器)，我认为您需要执行以下操作:

To accomplish this in SQL Server 2000 (without using a trigger), I think you would want to do something like this:

SET NOCOUNT ON;

SELECT TOP 10 key_column
INTO #foo
FROM dbo.myTable
ORDER BY some_logical_ordering_clause;

UPDATE dbo.MyTable
SET myBooleanColumn = 1
FROM #foo AS f
WHERE f.key_column = dbo.MyTable.key_column;

SELECT * FROM dbo.MyTable AS t
INNER JOIN #foo AS f
ON t.key_column = f.key_column;

如果你想要一个简单的查询，那么你可以有这个触发器:

If you want a simple query, then you can have this trigger:

CREATE TRIGGER dbo.upd_tr_myTable
ON dbo.myTable
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  SELECT * FROM inserted;
END
GO

请注意，此触发器无法判断您是在执行 TOP 10 更新还是其他操作，因此所有用户在执行更新时都会获得此结果集.即使您对 IF UPDATE(myBooleanColumn) 进行过滤，其他用户仍可能会更新该列.

Note that this trigger can't tell if you're doing your TOP 10 update or something else, so all users will get this resultset when they perform an update. Even if you filter on IF UPDATE(myBooleanColumn), other users may still update that column.

无论如何，您仍需要修正更新语句，以便知道要更新哪些行.(您甚至可以考虑使用 WHERE 子句.)

In any case, you'll still want to fix your update statement so that you know which rows you're updating. (You may even consider a WHERE clause.)

这篇关于SQL Server 2000 TOP 和 OUTPUT 关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！