I have a table with more than 3000 records. I need to replace a particular string of all the rows wid some other string But when i am using replace function Its only replacing half of the rows of the table while the other half are not getting replaced.
I m using the following query:

UPDATE    Table
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

Please let me know the solution to this!!

Thanks in advance!!

Recommended Answers

All 12 Replies

When you select using the WHERE in your query, do you get all the results or just the ones replaced?

While running the query i get the following message..


moreover There are 3000 records in the table and when i check the database after running this query only 300 records have been replaced rest are still same....

You are using a where, which means that you are filtering the records.
If you run a select with the same where, I'm pretty sure that you'll get 1127 records, so I don't see a problem with the result 1127 ROWS AFFECTED.

As for the 300 records only getting updated, I don't have an explanation. Can you tell us what SQL Server you are working on and the data type of Source?

I am using SQL SERVER 2005. And moreover i would like to highlight that the string that is to be replaced is contained in every row so that means even if i use where clause it should affect all the 3000 records in the table

Verify that your where returns true for all 3000 records by using it with a select.
If that also returns 1127 records and you don't find any reasons for this, check you don't have a SET ROWCOUNT in place (if you are not running the query directly from Management Studio).

PS: Check that there are no triggers messing up with your update.

...I m using the following query:

UPDATE    Table
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

Are you really using this exact query on SQL2005? When I try to run your code I get a syntax error because "Table" is a reserved word. Are you using "update [Table]" or something like that?

Also, I built a little table and populated it with some test data, and it worked fine. What's the table structure of [Table]? What are the datatypes? Unicode (nchar or nvarchar)?

Hi @adam I am running this query directly from management studio. Its seriusly strange that if I am trying to update a single record through where clause than also the record is not getting replaced. IS there any error in the replace function I am using????????

@BitBlt the word "Table" is just used for reference. the correct table name is "Quest_Bank" and the column name that is to be updated is "Source" which is varchar type.
so the correct query is:

UPDATE   Quest_Bank
SET Source = REPLACE(Source, 'src="~/','src ="http://www.mywebsite.co.in/')
WHERE     (Source LIKE '%src="~/%')

are you getting expected number of records by selecting it

select count(*) from quest_bank where source like '%src="~/%'

I think you might be having space in between in some records [ src = "~/ ] like that

@urtrivedi Yes I am getting the expected records after running SELECT count(*) FROM quest_bank WHERE source LIKE '%src="~/%'.

ANd I have checked there are no spaces in between

after keeping backup, i suggest you to run without where condition.

Now again after restoring the backup i tried running the same query it replaced all the records.. Hence my problem solved.

Thanks all :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.