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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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
52326 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000