CODEWITHSUNDEEP
sqlsql-server
user2142260
user2142260

Reputation: 31

Error : Subquery returned more than 1 value

My query to update a whole table looks like this:

update works set nIndex = cast(replace(replace((select strIndex 
 from works), char(10), ''), char(10), '') as int)

However when I run it I get this error:

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

.

Upvotes: 0

Views: 855

Answers (5)

MaxiWheat
MaxiWheat

Reputation: 6251

You could also add a TOP1 in your subquery, which would fix that error

update works set nIndex = cast(replace(replace((select TOP 1 strIndex 
from works), char(10), ''), char(10), '') as int)

But yet does not garrantee that the result is what you're expecting since we don't have the context.

Upvotes: 0

anon
anon

Reputation:

In addition to the other responses, I suspect you didn't mean to replace char(10) twice. What you probably want is to replace both char(10) and char(13):

UPDATE dbo.works 
  SET nIndex = CONVERT(INT, REPLACE(REPLACE(
    strIndex, CHAR(13), ''), CHAR(10), ''));

Of course since strIndex is a string, it could potentially contain other characters besides CR/LF, so you may want to add a WHERE clause:

WHERE ISNUMERIC(REPLACE(REPLACE(strIndex, CHAR(13), ''), CHAR(10), '')) = 1;

Or you could use a CASE expression in the update to make sure that incompatible values are set to NULL, e.g.:

UPDATE dbo.works 
  SET nIndex = CASE 
    WHEN ISNUMERIC(REPLACE(REPLACE(strIndex, CHAR(13), ''), CHAR(10), '')) = 1
  THEN 
    CONVERT(INT, REPLACE(REPLACE(
    strIndex, CHAR(13), ''), CHAR(10), '')) 
  ELSE NULL END;

In SQL Server 2012 this logic is much simpler, e.g. TRY_CONVERT().

Upvotes: 0

RGPT
RGPT

Reputation: 574

The error you are having basically says that you are trying to get several results to nIndex, because that's the result of: select strIndex from works

Forgetting all the casts and replaces you are saying: nIndex = (1, 2, 3, 4,...) That is not possible because you can only give one value there.

For this to work you would need to have something that limited the results: select strIndex from works WHERE PRIMARY_KEY = SOMETHING This would not be an efficient way to solve your problem, but it is for you to understand.

Since the two columns are in the same table this is not necessary at all, and you can simply do:

update works set nIndex = cast(replace(replace(strIndex, char(10), ''), char(10), '') as int)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You don't need the subselect. Try this:

update works
     set nIndex = cast(replace(replace(strIndex, char(10), ''), char(10), '') as int)

Your subselect was returning all the values of strIndex to update each row. That is way overkill. Just do the cast and replace on the values within one row.

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85056

As the error says, you are returning more than one record here:

select strIndex from works

So you are attempting to update a single value with every strIndex value in the works table. This will not work.

How to get this to work really depends on what you are attempting to do. Since you haven't gone into that here, I won't speculate on what would fix the error.

Upvotes: 1

Related Questions