嵌套更新SQL Server和SELECT

I need to have a query as below:

我需要查询如下：

SELECT top (1) @AddressRepeatNum=a.CheckNumber FROM(
UPDATE dbo.SearchList SET CheckNumber=CheckNumber+1 
OUTPUT inserted.CheckNumber AS CheckNumber 
WHERE PageAddress=@Address and CheckNumber<6
) as a

but it doesn't work. How should I rewrite it to work? In a simple word, I want to add one to a column of my table and then if it was larger than 5 then do something

但它不起作用。我应该如何重写它？简单来说，我想在我的表的一列中添加一个，然后如果它大于5则执行某些操作

1 个解决方案

#1

You cannot do a select directly on an update like that. You insert the output information to a table variable and then you select from the table variable.

您不能直接在这样的更新上进行选择。您将输出信息插入表变量，然后从表变量中选择。

DECLARE @AddressRepeatNum INT, @Address varchar (500) = 'Test'
DECLARE @Check table (checknumber INT)
UPDATE dbo.SearchList SET CheckNumber=CheckNumber+1 
OUTPUT inserted.CheckNumber into @Check
WHERE PageAddress=@Address and CheckNumber<6

SELECT TOP (1) @AddressRepeatNum=CheckNumber 
FROM @check 
ORDER BY CheckNumber