"subquery returns more than one value" NOT!

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 returns more than one value" NOT!

Author

Topic

macsterling
Yak Posting Veteran

56 Posts

Posted - 2008-11-23 : 20:29:39

When I run the following SQL I get the "subquery returns more than one value" message. However if I run just the subquery I get 35 unique rows (I have even added the key of the GLbasecodes when runing the subquery to insure no duplicates)
Any suggestions?

Update expensecodes set
BilledDirectBaseID= (
select baseid from GLbasecodes b
join xfer_Progl p on substring(p.proglrmb,1,4) = b.basecode
join xfer_category c on c.catglidn = p.proglidn
join expensecodes e on e.eccode = c.catidno
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 00:47:50

seems like what you want is this

Update e
set e.BilledDirectBaseID= b.baseid 
from GLbasecodes b
join xfer_Progl p on substring(p.proglrmb,1,4) = b.basecode 
join xfer_category c on c.catglidn = p.proglidn 
join expensecodes e on e.eccode = c.catidno

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 03:04:46

Even if there are no duplicates in glbasecodes, there are duplicates in the subquery when you join the glbasecodes to the other three tables.

Use Visakh's suggestion, or rewrite your attempt to this

Update expensecodes set
BilledDirectBaseID= (
select distinct baseid from GLbasecodes b
join xfer_Progl p on substring(p.proglrmb,1,4) = b.basecode
join xfer_category c on c.catglidn = p.proglidn
join expensecodes e on e.eccode = c.catidno
)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources