Solved

"Subquery returned more than 1 value". Need someone to check code!

Posted on 2013-10-30
2
431 Views
Last Modified: 2013-10-30
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())

Open in new window


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())

Open in new window


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

Open in new window


Thanks in advance for help!
0
Comment
Question by:dejandejanovic
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39611242
>SET @DiffDate = ...
The above assigns a value to a scalar variable, so whatever is in ... must return only one value.

If you're trying to UPDATE a set, lose the variables and try something like this..
UPDATE IsLogin
SET IsLogin = 'False', OnlineTime = convert(varchar(max), GetDate())
WHERE IsLogin = 'True' AND DATEDIFF(minute,(OnlineTime,GetDate())) > 1

Open in new window

0
 

Author Closing Comment

by:dejandejanovic
ID: 39611264
Crap, so simple. It seems I'm a compliate person :-)

thank you
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now