SQLTeam.com | Weblogs | Forums

Update Column Gives error Subquery returned more than 1 value


#1

Hi experts - i'm trying to update 1 columns with PART of the contents of another column.. in the same table.

Update [dbo].[KnownSQLServers]
Set ServerNameOnly = (SELECT SUBSTRING(ServerName,0, CHARINDEX('.',ServerName)) as 'new_name' from [dbo].[KnownSQLServers])

Gives error:

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

There must be an easy way to do this.
Thanks


#2

you may have duplicate of same name in dB.

Try this: Set ServerNameOnly = (SELECT TOP1 SUBSTRING(ServerName,0, CHARINDEX('.',ServerName)) as 'new_name' from .. see if this work?
Pasi


#3
Update [dbo].[KnownSQLServers]
Set ServerNameOnly = (
SELECT SUBSTRING(ServerName,0, CHARINDEX('.',ServerName)) as 'new_name' from [dbo].[KnownSQLServers])

so you want to rename all KnownSQLServers' ServerNameOnly column to the same value?


#4

Assuming its from the same row: -
Update [dbo].[KnownSQLServers]
Set ServerNameOnly = LEFT(ServerName,CHARINDEX('.',ServerName)-1)


#5

Thanks, everyone. Yes I want to update the same row. But I have multiple rows with the same value in the ServerName column.


#6

typically when this error comes

you will beforced to choose one row from all the rows being returned

how you do this .. depends !!
min()
max()
top 1
depends on what you are looking for

hope this helps
:slight_smile:
:slight_smile:


#7

else you can use

cross apply
or
top 1 with ties


#8

yosiasz - Your solution worked perfectly! It updated all rows correctly. Thanks.


#9

I was just asking using your own script which I believed was dangerous