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)

    Best way to fix spelling errors and eliminating duplicate rows?

    I am currently using this query to fix misspellings in my searches table:

    UPDATE searches SET keyword = REPLACE(keyword,'lavendar','lavender') WHERE keyword LIKE '%lavendar%';

    Many times I run into problems because of duplicate entries. This is because a user will search using the wrong spelling, and then they will search again with the correct spelling. I know how to use UPDATE IGNORE, to skip those problems but is there a way to do something like UPDATE DELETE? I would like to fix the spelling error, but if it causes a duplicate entry, then I would just like that row to be deleted.

    Convert your dollars into silver coins.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    best way to fix spelling errors -- don't let users type keywords, have them select from a dropdown list and use foreign keys to enforce

    best way to eliminate duplicate rows -- UNIQUE constraints

    there is no UPDATE DELETE -- you would have to code up the equivalent using application language (php or whatever) | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

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