CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 21

MySQL - UPDATE part of a string in a column

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!

Upvotes: 0

Views: 3924

Answers (3)

Andy
Andy

Reputation: 21

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%';

Upvotes: 2

Kendra H.
Kendra H.

Reputation: 38

Does this not work?

Set Body = 'ReplaceWithThisText' ?

Upvotes: 1

itoctopus
itoctopus

Reputation: 4271

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.

Upvotes: 2

Related Questions