big riddle... - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

big riddle...

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-08-11 : 05:12:02

Hi, this alreafy costed me 10hours today...

the query analyser gives the following error message:

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

This is the query:

UPDATE tblIMISTravelExtras
SET tblIMISTravelExtras.Division = (
SELECT tblCurrentDivision.Division FROM (tblIMISTravelSum JOIN tblCurrentDivision ON tblIMISTravelSum.OrgIDMain = tblCurrentDivision.UNOrgUnitCode)
WHERE (tblIMISTravelExtras.TravelID = tblIMISTravelSum.TravelID))
WHERE tblIMISTravelExtras.Division IS NULL

So, there can't be multiple records in the subquery because TravelID is the primary Key in both tables tblIMISTravelExtras and tblIMISTravelSum.

Does anyone here has an idea what the matter is???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 05:18:08

so why don't you just run the select query on its own and see what it returns?

Go with the flow & have fun! Else fight the flow :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-11 : 05:22:25

I agree. From what you've said, the multiple records must be a result of the join to tblCurrentDivision.

Mark

vux
Starting Member

45 Posts

Posted - 2004-08-13 : 00:31:35

HI guys,

how should the select query look like?

I tried that one before but its does not go through:
SELECT tblIMISTravelExtras.TravelID,tblIMISTravelExtras.Division, tblCurrentDivision.Division
FROM tblIMISTravelExtras.TravelID,
(tblIMISTravelSum JOIN tblCurrentDivision ON tblIMISTravelSum.OrgIDMain = tblCurrentDivision.UNOrgUnitCode)
WHERE tblIMISTravelExtras.TravelID = tblIMISTravelSum.TravelID
AND tblIMISTravelExtras.Division IS NULL

How can the join create multiple records, its an INNER JOIN?

thanks for your help!

vux
Starting Member

45 Posts

Posted - 2004-08-13 : 00:49:12

i got it!

the tblCurrentDivision.UNOrgUnitCode appeared twice for one code! but i can pick the right row since there is a flag

now its ok

thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources