"subquery returned more than one value" error

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

SQL Server 2005 Forums

Transact-SQL (2005)

"subquery returned more than one value" error

Author

Topic

gs
Starting Member

14 Posts

Posted - 2008-02-21 : 09:55:08

I am getting an error which is
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

This query is a simple update statement within a subquery. Without the update statement when I just test the select statements, I get the desired answer. What am I doing wrong with the update statement using a subquery.
Any help will be appreciated.

UPDATE test
SET off_8_max =(Select maxi=max(t.l)
FROM (select year_ID,mnth_ID,day_ID,l=load_1
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_2
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_3
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_4
FROM test
) t, temp_class c
WHERE t.year_ID=c.year_ID
AND t.mnth_ID=c.mnth_ID
AND t.day_ID=t.day_ID
GROUP BY t.year_ID,t.mnth_ID,t.day_ID)

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 10:02:38

[code]UPDATE t1
SET off_8_max = t2.maxi
from test t1 join
(
Select t.year_ID,t.mnth_ID,t.day_ID, max(t.l) as maxi
FROM
(select year_ID,mnth_ID,day_ID,l=load_1
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_2
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_3
FROM test
UNION ALL
SELECT year_ID,mnth_ID,day_ID,l=load_4
FROM test
) t join temp_class c on t.year_ID=c.year_ID AND t.mnth_ID=c.mnth_ID AND t.day_ID=t.day_ID
GROUP BY t.year_ID,t.mnth_ID,t.day_ID
) t2
on t1.year_ID = t2.year_ID and t1.mnth_ID = t2.mnth_ID and t1.day_ID = t2.day_ID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

gs
Starting Member

14 Posts

Posted - 2008-02-21 : 10:28:47

Thank you . it worked like a charm.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-21 : 13:18:46

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources