Update Column Gives error Subquery returned more than 1 value

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.

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?

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?

1 Like

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

1 Like

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

typically when this error comes

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

how you do this .. depends !!
top 1
depends on what you are looking for

hope this helps

else you can use

cross apply
top 1 with ties

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

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

1 Like