SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Subquery returned more than 1 value.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dejanc
Starting Member

Slovenia
8 Posts

Posted - 10/30/2013 :  07:51:51  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/30/2013 :  08:01:23  Show Profile  Reply with Quote
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

Slovenia
8 Posts

Posted - 10/30/2013 :  08:15:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/30/2013 :  08:15:53  Show Profile  Reply with Quote
whats the datatype of OnlineTime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000