CODEWITHSUNDEEP
sqlsql-server
Daniel Scarlett
Daniel Scarlett

Reputation: 1

SQL - Subquery returned more than 1 value

BEGIN TRANSACTION

DELETE FROM MessagesRead 
WHERE [To] <> 'Feadmin'
  AND [To] <> 'Catalonia'
  AND [To] <> 'Bromordia'
  AND [From] <> 'Feadmin'
  AND [From] <> 'Catalonia'
  AND [From] <> 'Bromordia'
  AND MessageID IN (SELECT MessageID 
                    FROM Messages 
                    WHERE TickNr < ((SELECT TickNumber FROM CurrentTick)-1000))
  AND Saved = 0 AND [Read] =1;

DELETE FROM Messages 
WHERE MessageID NOT IN (SELECT DISTINCT MessageID FROM MessagesRead);

DELETE FROM MessagesAdmin 
WHERE MessageID NOT IN (SELECT DISTINCT MessageID FROM Messages);

DELETE from DiscussionMessages 
WHERE DATEDIFF(DAY,Inserted,CURRENT_TIMESTAMP) > 6000

COMMIT TRANSACTION

I've just upgraded from SQL Server 2012 to 2014. I'm running the above stored procedure which has worked until now getting the error message:

Msg 512, Level 16, State 1, Procedure Engine_DeleteMessages, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have obviously worked out it shouldn't say WHERE TickNr < but I can't think of a better way to word this, or why it was working until I upgraded?

Upvotes: 0

Views: 398

Answers (3)

jpw
jpw

Reputation: 44921

If you want to ensure that TickNr is lower than any possible TickNumber in CurrentTick this might be a good case for the ALL operator:

AND MessageID IN (SELECT MessageID 
                    FROM Messages 
                    WHERE TickNr < ALL((SELECT TickNumber FROM CurrentTick)-1000))

This would test the TickNr against all values returned in the subquery.

If there can/should only be one row returned you could use top 1 in the subquery to ensure that the subquery returns a single value.

Upvotes: 0

Dan
Dan

Reputation: 11114

Thats a pretty good error message, pretty explanatory. So you need the subquery to return a single value/row, by using a where clause or TOP.

Either: 

SELECT TickNumber FROM CurrentTick WHERE id = <someID>;

OR:

SELECT TOP 1 TickNumber FROM CurrentTick [ORDER BY some_col]

Upvotes: 3

RobertoGuzman
RobertoGuzman

Reputation: 75

Do you have multiple rows in CurrentTick? If there was once only one row in that table, then the above query would have worked. Could it be that there are now 2 or more rows in that table?

Upvotes: 0

Related Questions