Subquery returned more than 1 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

General SQL Server Forums

New to SQL Server Programming

Subquery returned more than 1 value error

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-18 : 11:47:55

A Table has many line items to one, so I tightened up the criteria so it would only return one line item per id. Demo table is one line item per record.

SELECT A.ID, A.TYPE, Max(A.DATE)
AS MaxOfDATE, DEMO.EXPIRE
FROM A INNER JOIN DEMO
ON A.ID = DEMO.ID
GROUP BY A.ID, A.TYPE, DEMO.EXPIRE
HAVING (((A.TYPE)='order') AND ((Max(A.DATE))<> DEMO.EXPIRE))

ID TYPE MaxDATE EXPIRE
13 ORDER 2010-08-16 00:00:00.000 2010-12-31 00:00:00.000
15 ORDER 2011-08-25 00:00:00.000 2011-08-16 00:00:00.000

I need to eb able to update the expire date with the Max Date, but the query below throws an error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

update DEMO
set DEMO.EXPIRE = (SELECT Max(A.DATE)
FROM A INNER JOIN DEMO
ON A.ID = DEMO.ID
GROUP BY A.ID, A.TYPE, DEMO.EXPIRE
HAVING ((A.TYPE)='order') AND (Max(A.DATE))<> DEMO.EXPIRE)

Thanks!

dz

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 12:18:56

what's the primary key of he table?

Post the DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-18 : 12:35:14

Primary keys:

Table A = SEQN
Table Demo = ID

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 12:47:22

So it's a 1-1 relationship?

I think you're missing something


update d
set d.EXPIRE = (SELECT Max(A.DATE) 
FROM A INNER JOIN DEMO d
ON A.ID = d.ID
GROUP BY A.ID, A.TYPE, d.EXPIRE
HAVING ((A.TYPE)='order') AND (Max(A.DATE))<> d.EXPIRE)

Perhaps

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-08-18 : 12:55:19

or maybe:


UPDATE D
SET EXPIRE = V.MaxDate
FROM DEMO D
	JOIN
	(
		SELECT A.ID, Max(A.DATE) AS MaxDate
		FROM A
		WHERE A.TYPE = 'order'
		GROUP BY A.ID
	) V
		ON D.ID = V.ID
WHERE D.EXPIRE <> V.MaxDate

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-18 : 13:52:40

That worked, but what is the V table a temp table? Can you point me to any links/articles on how to do this so I can get a better understanding?

Thanks,
dz

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 13:59:42

quote:
Originally posted by dzabor

That worked, but what is the V table a temp table? Can you point me to any links/articles on how to do this so I can get a better understanding?

Thanks,
dz

Yes...and no...it's called a derived table..and like a temp, it's in tempdb..unless they changed it, then it's in memory

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-18 : 14:31:29

Small correction -- A derived table is stored in memory, not tempdb.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources