Query that fixes mis-spellings leads to error
I run the following query to fix mis-spelled keyword searches in my database:
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?
UPDATE searches SET keyword = REPLACE(keyword,'respritory','respiratory') WHERE keyword LIKE '%respritory%';
1. Turn off the unique index that prevents duplicates from being saved in the table
2. Run the above query to fix the mis-spelled words
3. Turn the unique index back on
4. 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.