Problem with UPDATE and REPLACE

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)

Problem with UPDATE and REPLACE

Author

Topic

cnaypi
Starting Member

22 Posts

Posted - 2008-06-02 : 13:28:05

My issue is this. I have a total of 23000 rows in my Table. Now I'm trying to Replace any rows under the codeabbreviation column that has the word Protocol with the Abbreviation Prtcl. The problem that I'm running into is that When I use the UPDATE/REPLACE statement below I get the message that "23000 rows were affected" even though there are only total of 10rows that has the word Protocol. Shouldn't the correct message be "10 rows were affected" since the statement only did update 10 rows?

Update ProdDesc
Set Codeabbreviation = replace(codeabbreviation, 'protocol','Prtcl')

Here is my Table structure: ID is my Primary key
ID int
CodeId nvarchar(50)
CodeCategoryId nvarchar(50)
CodeCategory nvarchar(50)
CodeCategoryName nvarchar(50)
Code nvarchar(50)
CodeName nvarchar(250)
CodeAbbreviation nvarchar(250)
FullTextName nvarchar(250)
Suggestedabbreviation nvarchar(72)
ModifiedDate datetime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:32:12

It will affect all rows as you havent specified any condition. But it will perform the replace only on 10 rows as they only have the word protocol. try like this and see if you're getting 10 rows affected

Update ProdDesc
Set Codeabbreviation = replace(codeabbreviation, 'protocol','Prtcl')
WHERE PATINDEX('%protocol%',codeabbreviation) >0

cnaypi
Starting Member

22 Posts

Posted - 2008-06-02 : 14:54:31

Thanks Visakh! It is now working properly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:01:41

quote:
Originally posted by cnaypi

Thanks Visakh! It is now working properly.

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources