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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -