CODEWITHSUNDEEP
sqlsql-serverdatabase
Nick Mehrdad Babaki
Nick Mehrdad Babaki

Reputation: 12535

Nested update SQL Server and 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

Upvotes: 0

Views: 66

Answers (1)

HLGEM
HLGEM

Reputation: 96580

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

Upvotes: 4

Related Questions