MySQL - UPDATE part of a string in a column

Question

I am trying to replace some text in a column - text that I have confirmed exists. When I run the query:

SELECT Body FROM dbname.tcontent 
where body like '%http://local.website.org%'
and display=1
and approved=1;

it returns 359 records. So, I was expecting 359 records to be changed when I ran this query:

update dbname.tcontent 
set Body = replace(Body, 'http://local.website.org', '/foldername') 
where instr(Body, 'http://local.website.org') > 0 
and display=1 
and approved=1;

However, the query found 359 matches, but updated no columns. The message reads: "0 row(s) affected Rows matched: 359 Changed: 0 Warnings: 0. I'm pretty sure I've got the SQL correct as I've confirmed it from at least three sources. I have read that the 359 "matches" are based on records matching the WHERE clause, and so I believe the problem is with that second line:

set Body = replace(Body, 'http://local.website.org', '/foldername')

even though the SQL is correct. I've unchecked "Safe Updates" in my preferences just to be sure, and still, no rows are being updated.

I still think that maybe the slashes and/or dots in the text I am replacing are tripping up the replace method despite an example of doing what I want to do here.

Thanks in advance for any assistance!

itoctopus · Accepted Answer · 2015-10-06 20:19:39Z

2

Try this query instead:

UPDATE `dbname`.`tcontent` 
SET `body`= REPLACE(`body`, 'FindThisText', 'ReplaceWithThisText') 
WHERE `body` like '%FindThisText%' AND `display`=1 and `approved`=1;

Always backup your table before running such UPDATE queries.

answered Oct 6, 2015 at 20:19

itoctopus

4,2915 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andy Over a year ago

Thanks for your response. I should have added that I also tried that UPDATE statement. Same result: "0 row(s) affected Rows matched: 359 Changed: 0 Warnings: 0"

itoctopus Over a year ago

What if you try to update a single row? Can you do that?

Andy Over a year ago

I sure can: UPDATE table set SiteID = 'abc' where tcontentid = 'def' : "1 row(s) affected Rows matched:1 Changed:1 Warnings:0. Good thought, though. Thanks again for posting.

Andy Over a year ago

However, I cannot perform an UPDATE to one row using REPLACE, as follows: UPDATE dbname.tcontent set Body = REPLACE(Body, 'FindThisText', 'ReplaceWithThisText') where instr(Body, 'FindThisText') > 0 AND TCONTENT_ID = 12345;

AdamMc331 Over a year ago

@Andy that query worked for me in SQL Fiddle. Are you sure there's a row that meets all of the criteria you need?

Andy · Accepted Answer · 2015-10-07 20:58:56Z

2

For whatever reason, I had to break it down into two statements. First Update statement replaced http:// with nothing, and the second replaced the remainder of the URL:

update database.tcontent set Body = replace(Body, 'http://', '')  where Body like '%http://local.website.org%';
update database.tcontent set Body = replace(Body, 'local.website.org', '/foldername')  where Body like '%local.website.org%';

answered Oct 7, 2015 at 20:58

Andy

211 silver badge5 bronze badges

Comments

Kendra H. · Accepted Answer · 2015-10-06 23:06:10Z

1

Does this not work?

Set Body = 'ReplaceWithThisText' ?

answered Oct 6, 2015 at 23:06

Kendra H.

387 bronze badges

1 Comment

Andy Over a year ago

Hi kendra. That exact query (with a where clause) works. The row is updated. However, when invoking the REPLACE method on the Body column to replace partial text that I know exists, no rows are updated :-(

Collectives™ on Stack Overflow

MySQL - UPDATE part of a string in a column

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related