I run the following query to fix mis-spelled keyword searches in my database:
<font face='Comic Sans MS'>UPDATE searches SET keyword = REPLACE(keyword,'respritory','respiratory') WHERE keyword LIKE '%respritory%';</font>
Occasionally a person with have both an incorrect spelling of their search and a correct one. So when I run the above query, it will error out because of the unique index I have setup for the uID, type and keyword columns. What is the best way to handle this situation? Possibly do the following?
- Turn off the unique index that prevents duplicates from being saved in the table
- Run the above query to fix the mis-spelled words
- Turn the unique index back on
- Run another query that will look for duplicates from the same user ID in the keyword column and delete one of them
Any advice would be helpful.
I would switch 3 and 4, adding a unique index to a table with duplicate key values will give an error if I'm not mistaken,