0

Hi,

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!!

4
Contributors
12
Replies
13
Views
6 Years
Discussion Span
Last Post by arsheena.alam
0

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

0

While running the query i get the following message..

1127 ROWS AFFECTED.

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....

0

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?

Edited by adam_k: n/a

0

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

0

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.

0

...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)?

0

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="~/%')
0

are you getting expected number of records by selecting it
say

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

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

Edited by urtrivedi: n/a

0

@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

0

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

Thanks all :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.