SQL to Delete Duplicate posts in VB 3.8

[newmomsforum]

Hi Guys

I've been trying to use the following SQL command to delete duplicate posts in a single thread:

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

The SQL query was originally posted by Wayne here although this was for an earlier version of VB:

http://www.vbulletin.com/forum/showthread.php?t=285501

Unfortuantly, when I run this query on version 3.8 of my boards I get the following error message:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1064
error desc: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT threadid, dateline, username, userid, MIN( postid ) AS m

Does anyone have any ideas what will work on my boards :confused:

Many many thanks in advance

Regards

Mark

[newmomsforum]

Sorry to bump guys but my forums are looking really messy after a failed import. :(

Any ideas or suggestions greatly appreciated. :)

Cheers

Mark

[gmerin]

just a suggestion: re-type the query making sure you have no spurious characters in the text and no extra whitespace at the end of the lines (which sometimes happens when you cut & paste) because when i run the query from your post against my dbms from the sql commandline, I don't get any sql errors:

[SQL] 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

Affected rows: 0
Time: 0.098ms

not saying the sql is correct for the task you want to accomplish, just that it's not throwing errors for me.

[FlyingBurrito4]

Where would I add this SQL?