Having problems with an update / select statement!

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)

Having problems with an update / select statement!

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 05:52:02

Hi All

I have the following query:

update tblCallHistory
set fldCallHistory_Status = 'C'
where fldCallHistory_ID = (select callid from tstMRSUpdate)

When I run this I am getting 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.
The statement has been terminated.

Is there any way around this?

Thanks for your help!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 05:57:54

Don't use =, use IN operator

update tblCallHistory 
set fldCallHistory_Status = 'C' 
where fldCallHistory_ID in (select callid from tstMRSUpdate)

Harsh Athalye
India.
"Nothing is Impossible"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-09 : 06:04:23

Or even better, use a join

UPDATE ch
SET    ch.fldCallHistory_Status = 'C'
FROM   dbo.tblCallHistory AS ch
JOIN   dbo.tstMRSUpdate AS mu
ON     mu.callid = ch.fldCallHistory_ID

Mark

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 06:20:38

Still better, use EXISTS operator:

update x
set fldCallHistory_Status = 'C' 
from tblCallHistory x
where Exists ( select * from tstMRSUpdate y where y.callid = x.fldCallHistory_ID )

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources