Mysql – UPDATE SET REPLACE() matches but does not change

MySQLmysql-5.1replaceupdate

I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own…besides, the other post did not help me. What I am trying to do is pretty straight-forward.

To be on the safe side, I tried a simple UPDATE statement on a different table, which was successful. So, here's my problem:

mysql> UPDATE bugs_fulltext SET short_desc =
REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') WHERE bug_id=452;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

I'm not using wildcards nor am I using a LIKE statement. I am telling it which record to update. Why isn't my text changed?

Best Answer

Look carefully at the query's output

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The expression Rows matched is based on WHERE bug_id=452;. That matched.

When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment means.'

To verify that run this:

SELECT REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') replace_result
FROM bugs_fulltext WHERE bug_id=452;

If you getback the old value, then the old value was not in the row to begin with.

You should just directly update it (overwrite it):

UPDATE bugs_fulltext
SET short_desc = 'I don\'t know what my previous comment means.'
WHERE bug_id=452;
Related Question