How to prevent this error - 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 2005 Forums

Transact-SQL (2005)

How to prevent this error

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-06-17 : 06:49:52

Hi,

I have this

UPDATE tblLomCpLookup
SET shortCapCode = (SELECT cder_id FROM PUB_CAR..CapDer WHERE REPLACE(PUB_CAR..CapDer.cder_capcode,' ','') = tblLomCpLookup.CapCode)

Error

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.

How can I make it so it doesn't update the record if the subquery returns more than 1 record?

Thanks

asgast
Posting Yak Master

149 Posts

Posted - 2009-06-17 : 07:01:32

our query returns more than 1 value, while only 1 is allowed

UPDATE tblLomCpLookup
SET shortCapCode = (SELECT TOP 1 cder_id FROM PUB_CAR..CapDer WHERE REPLACE(PUB_CAR..CapDer.cder_capcode,' ','') = tblLomCpLookup.CapCode)

but its better to rethink our logic and use INNER JOIN instead of sub select in this case.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-17 : 07:01:38

As there is no where-clause in the update I assume you want to update for all rows? In thatt case you can use a join instead. But mid you; it will update all rows in the table! ->

UPDATE a
  SET a.shortCapCode = b.cder_id
FROM tblLomCpLookup a
  INNER JOIN PUB_CAR..CapDer b
    ON REPLACE(b.cder_capcode,' ','') = a.CapCode

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources