busboy — 2012-10-05T23:33:04-04:00 — #1
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.
r937 — 2012-10-06T06:44:42-04:00 — #2
find the two entries manually, and delete one of them
lemon_juice — 2012-10-07T16:18:31-04:00 — #3
Temporarily remove PRIMARY/UNIQUE index on the 'keyword' column.
Run your UPDATE statement to remove apostrophes.
Find duplicate values for 'keyword' with this query:
SELECT keyword, COUNT(keyword) AS c
GROUP BY keyword
Manually replace (or delete) all duplicate keywords so that all values are unique (= the above query returns 0 rows).
Recreate back the PRIMARY or UNIQUE index on 'keyword'.