SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dublicate posts, how to remove 1 and leave one?

    Hi,

    actually i have asked this question in many pleases with no luck
    fianlly some one guided me here to seek an advice...please..

    i have run the following query in the database
    Code:
    SELECT bad_rows. * FROM post AS bad_rows
    INNER JOIN (
    SELECT threadid, dateline, username, userid, MIN( postid ) AS min_postid FROM post
    GROUP BY threadid, dateline, userid
    HAVING count( * ) >1
    ) AS good_rows ON good_rows.threadid = bad_rows.threadid
    AND good_rows.dateline = bad_rows.dateline
    AND good_rows.userid = bad_rows.userid
    AND good_rows.min_postid <> bad_rows.postid
    in phpmyadmin, it displayed the results of 4000 duplicate entries
    i have tried to delete them, with no luck...

    any idea how to introduce some changes to the code, or inject another code to delete the duplicate entries (leaving one of them)

    ---------
    in simple example
    there is an two records (two rows) which shares different id, but both share exactly the duplicate contents including the columns vlauses (also dublicate) , time of the post (also dublicate) etc.. the only difference is the id only.

    any idea please?

  2. #2
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. SELECT UNIQUE allows you to select only ONE record if there are two exact records. So just use UNIQUE keyword and delete those. You will thus be left with only 1 copy of each record.

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks svcghost

    sorry but i am not that mysql expert, do you mean i would need to delte the dublicate records one by one? I have 4000 records and will be lot of time, any chance for a little code to do that automatically, please?

    Quote Originally Posted by svcghost View Post
    Yes. SELECT UNIQUE allows you to select only ONE record if there are two exact records. So just use UNIQUE keyword and delete those. You will thus be left with only 1 copy of each record.
    P.S
    every time i try deleting a record from the result of the above code
    Code:
    SELECT bad_rows. * FROM post AS bad_rows
    INNER JOIN (
    SELECT threadid, dateline, username, userid, MIN( postid ) AS min_postid FROM post
    GROUP BY threadid, dateline, userid
    HAVING count( * ) >1
    ) AS good_rows ON good_rows.threadid = bad_rows.threadid
    AND good_rows.dateline = bad_rows.dateline
    AND good_rows.userid = bad_rows.userid
    AND good_rows.min_postid <> bad_rows.postid
    i will get the error message:
    Code:
    #1054 - Unknown column 'bad_rows.postid' in 'where clause'


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
  •