-1

I would like to add the capability to the following code so that it also deletes posts that have the same meta key value. In other words, there is a meta key 'source_link' and I would like to delete duplicates that have the same value for 'source_link'. This is the current code:

$wpdb->query("
    DELETE double_posts.*
    FROM $wpdb->posts as double_posts
    INNER JOIN (
    SELECT post_title, MIN(id) as min_id
    FROM $wpdb->posts
    WHERE (post_status = 'publish'
    AND post_type = 'post')
    OR (post_status = 'published'
    AND post_type = 'post')
    GROUP BY post_title
    HAVING COUNT(*) > 1
    ) AS orig_posts ON orig_posts.post_title = double_posts.post_title
    AND orig_posts.min_id <> double_posts.id
    ");

Currently it jsut deletes posts with duplicate post titles. I would like to keep that, then add the deleting by duplicate meta key value. Any help here would be great! Thanks.

arian1123
  • 216
  • 3
  • 14
  • I just updated the post. The code works well howerver it only removes posts with duplicate titles. I would like it to remove posts by duplicate title AND the meta key value. Im asking about the latter. – arian1123 Nov 01 '14 at 00:24
  • Try to collect all ids of posts for deletion and use [wp_delete_post](http://codex.wordpress.org/Function_Reference/wp_delete_post), since that function will delete, along with the post, all data associated to that post. – Danijel Nov 01 '14 at 00:56
  • your sql only selects one of the duplicates which is fine if you have 2, but if you have 3 or more... – doublesharp Nov 01 '14 at 00:57
  • @doublesharp ok i didnt realize that, would you have a fix for that as well? – arian1123 Nov 01 '14 at 00:59
  • Deleting by duplicate meta key is a little trickier without having something to test it on, give me a sec. – doublesharp Nov 01 '14 at 01:13

1 Answers1

0

Your inner query is only getting the MIN(id) which means only one of possibly many duplicates will be included in your delete. There are also several approaches to this - if you want to enforce it going forward you might consider a database unique key index.

Make sure to back up your database before running these queries in case something goes wrong.

To delete all duplicates by post_title:

DELETE p1 
FROM 
    {$wpdb->posts} p1, 
    {$wpdb->posts} p2 
WHERE 
    p1.ID > p2.ID 
    AND p1.post_title = p2.post_title

To delete all posts by duplicate meta_key:

DELETE p, pm1
FROM 
    {$wpdb->posts} p, 
    {$wpdb->postmeta} pm1, 
    {$wpdb->postmeta} pm2 
WHERE 
    p.ID = pm1.post_id
    AND pm1.post_id > pm2.post_id 
    AND pm1.meta_key = 'source_link' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value
doublesharp
  • 26,888
  • 6
  • 52
  • 73
  • I see OP query is deleting all ids which are not equal to minimum id, which is correct – radar Nov 01 '14 at 01:42
  • That's correct, now that i look at it again, still a lot more verbose though. – doublesharp Nov 01 '14 at 02:06
  • Would this remove all duplicates or just 1 duplicate? Im not an sql expert my any means so correct me if im wrong, but seems you are just comparing 2 posts. I would need this code to remove all of the duplicates. Thanks – arian1123 Nov 03 '14 at 01:59
  • All the duplicates, it's comparing two joined tables, one of which contains the duplicates. Make a backup of your data and test it. – doublesharp Nov 03 '14 at 03:22
  • I run it and I get an error: Error establishing connection to the database. Also thanks! – arian1123 Nov 03 '14 at 03:40
  • if it helps, I'm trying to call the sql using: $wpdb->query(" sql here "); – arian1123 Nov 03 '14 at 03:41
  • Make sure you call `global $wpdb` - it uses the default WP database connection, so if it's not connecting you have bigger issues... – doublesharp Nov 03 '14 at 05:14
  • Ok so I got it to do something... everytime I run the sql I can no longer open my wegpage with the error: "error establishing connection to databse". When I reset MySQL my site will load fine again, but to no avail in removing posts. – arian1123 Nov 04 '14 at 00:12
  • how can I order this query so it only deletes the most recent posts? currently it deletes all except the most recent duplicate - instead of keeping the original... – Benedict Harris Dec 03 '22 at 03:06
  • @BenedictHarris https://stackoverflow.com/a/40636568/1427161 – doublesharp Dec 06 '22 at 06:28