Trying to delete all apostrophes from saved searches

When I run this query:

UPDATE searches SET keyword = REPLACE(keyword,“'”,“”) WHERE keyword LIKE “%'%”;

I get this error:

Duplicate entry ‘1039-parkinsons-basic’ for key ‘uID’ – 647 ms

What is the best way to overcome this? I’ve changed the search code so that new searches are stripped of apostrophes. Now I’m just trying to clean up what’s in the database.

Thanks!

find the two entries manually, and delete one of them

  1. Temporarily remove PRIMARY/UNIQUE index on the ‘keyword’ column.

  2. Run your UPDATE statement to remove apostrophes.

  3. Find duplicate values for ‘keyword’ with this query:


SELECT keyword, COUNT(keyword) AS c
FROM searches
GROUP BY keyword
HAVING c>1;

  1. Manually replace (or delete) all duplicate keywords so that all values are unique (= the above query returns 0 rows).

  2. Recreate back the PRIMARY or UNIQUE index on ‘keyword’.