Dublicate posts, how to remove 1 and leave one?

Databases
1

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

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

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

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?

P.S
every time i try deleting a record from the result of the above 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:

#1054 - Unknown column 'bad_rows.postid' in 'where clause'