SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Trying to delete all apostrophes from saved searches

    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.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    What is the best way to overcome this?
    find the two entries manually, and delete one of them
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    1. Temporarily remove PRIMARY/UNIQUE index on the 'keyword' column.

    2. Run your UPDATE statement to remove apostrophes.

    3. Find duplicate values for 'keyword' with this query:
    Code:
    SELECT keyword, COUNT(keyword) AS c
    FROM searches
    GROUP BY keyword
    HAVING c>1;
    4. Manually replace (or delete) all duplicate keywords so that all values are unique (= the above query returns 0 rows).

    5. Recreate back the PRIMARY or UNIQUE index on 'keyword'.


Bookmarks

Posting Permissions

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