Subquery returned more than 1 value.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Subquery returned more than 1 value.

Author

Topic

dejanc
Starting Member

8 Posts

Posted - 2013-10-30 : 07:51:51

Hi,
I have below table, and query which return error. I was trying to add IN operator, but it seems that my knowledge is not good enough.

ID IsLogin OnlineTime
John True 2013-10-30 12:00:00
James True 2013-10-30 12:00:00

select DATEDIFF(minute,(SELECT OnlineTime FROM IsLogin WHERE IsLogin = 'True'),GETDATE())

And, error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

With IN operator, which of course also not working:

SELECT DATEDIFF(minute,(SELECT OnlineTime FROM IsLogin WHERE IsLogin IN ('True')),GetDate())

This is my entire query inside of stored procedure, which works fine if only 1 row is found.

DECLARE @DiffDate varchar(max)
SET @DiffDate = DATEDIFF(minute,(SELECT OnlineTime FROM IsLogin WHERE IsLogin = 'True'),GetDate())
UPDATE IsLogin
SET IsLogin = 'False', OnlineTime = convert(varchar(max), GetDate())
WHERE IsLogin = 'True' AND @DiffDate > 1

Thanks in advance for help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:01:23

you just need this


UPDATE IsLogin
SET IsLogin = 'False', 
OnlineTime = convert(varchar(max), GetDate())
WHERE IsLogin = 'True' 
AND OnlineTime <= DATEADD(minute,-1,GETDATE())

Also I dont recommend the code in blue. why should you convert date value to varchar(max)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dejanc
Starting Member

8 Posts

Posted - 2013-10-30 : 08:15:16

In the middle of project I was getting below error. And, I just did not investigate further since did not causing problems.
Conversion failed when converting date and/or time from character string.

UPDATE IsLogin
SET IsLogin = 'False', OnlineTime = GetDate()
WHERE IsLogin = 'True' AND DATEDIFF(minute,(OnlineTime,GetDate())) > 1

Thank you for help. It seems I was to complicating with query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 08:15:53

whats the datatype of OnlineTime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources