1064 - You have an error in your SQL syntax (Deleting duplicates)

[strago]

Datebase: blog

Table: nitendoposts

<?php

function clearDuplicatePosts(){	
global $wpdb;
$wpdb->query("
delete bad_rows.*
from $wpdb->nitendoposts as bad_rows
inner join (
select post_title, MIN(id) as min_id
from $wpdb->nitendoposts
group by post_title
having count(*) > 1
) as good_rows on good_rows.post_title = bad_rows.post_title
and good_rows.min_id <> bad_rows.id;
");
}

add_action('publish_post', 'clearDuplicatePosts');
?>

Does nothing. When I try to run it from phpMyAdmin using

 

DELETE bad_rows .  *  FROM nitendoposts AS bad_rows INNER JOIN (
SELECT post_title, MIN( id ) AS min_id
FROM nitendoposts
GROUP BY post_title
HAVING count( * ) >1
) AS good_rows ON good_rows.post_title = bad_rows.post_title
AND good_rows.min_id <> bad_rows.id

 

I get

 

#1064 - 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 post_title, MIN( id ) AS min_id

FROM nitendoposts

GROU

 

How do you get the script to remove duplicates by searching through the 'post_title' field?

[fenway]

And the query version of this statement works?

[strago]

That's the full script. It's a word press add-on.

[strago]

<?php
/*
Plugin Name: Duplicate Posts Eraser

*/

function simpleDuplicatePosts(){    
    global $wpdb;
    $wpdb->query("
delete from posts
USING posts, posts as vtable
WHERE (posts.ID > vtable.ID)
AND (posts.post_title=vtable.post_title)
    ");
}

add_action('publish_post', 'simpleDuplicatePosts');
?>

 

works...if you don't have a table prefix.

 

From the config.php file....

 

$table_prefix  = 'WHATEVER';

 

How do you make this script check for a $table_prefix like WHATEVERposts?

[fenway]

That's the full script. It's a word press add-on.

So you didn't write it?