SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Provo, UT
    1 Post(s)
    0 Thread(s)

    Query that fixes mis-spellings leads to error

    I run the following query to fix mis-spelled keyword searches in my database:

    UPDATE searches SET keyword = REPLACE(keyword,'respritory','respiratory') WHERE keyword LIKE '%respritory%';
    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?

    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.

    Thank you.
    Convert your dollars into silver coins.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    163 Post(s)
    4 Thread(s)
    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,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts