[FIXED] Delete duplicate post with same meta data

Issue

I'm using WordPress. I would like to delete duplicate posts from the wp_post table. The condition for determining whether a post is duplicate or not is based on whether two different meta keys have the same meta values.

I came across this query to delete post that have a single meta value being duplicated:

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 = '_email' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value

What I would like to achieve is something like this:

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 = '_email' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value

    AND pm1.meta_key = '_list' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value

I tried running the following:

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 = '_email' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value
    AND p.ID IN (
        SELECT 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 = '_list' 
            AND pm1.meta_key = pm2.meta_key 
            AND pm1.meta_value = pm2.meta_value
    )

but got an error message on MySQL. What am I doing wrong?

Any feedback is appreciated! Thanks!

Solution

Looking at your code, you can't have a meta_key = '_email' AND = '_list'. It could be you mean meta_key = '_email' OR = '_list'. You should not use the old implicit join syntax based on where but use a more readable explicit JOIN syntax:

DELETE p, pm1
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm1 ON p.ID = pm1.post_id 
  AND  ( pm1.meta_key = '_email'  OR  pm1.meta_key = '_list' )
INNER JOIN {$wpdb->postmeta} pm2 ON pm1.post_id > pm2.post_id 
   AND pm1.meta_value = pm2.meta_value 
      AND pm1.meta_key = pm2.meta_key 

Anyway, in your query you have a wrong sub-query select; try p.ID instead of p, pm1:

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 = '_email' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value
    AND p.ID IN (
        SELECT p.ID 
        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 = '_list' 
            AND pm1.meta_key = pm2.meta_key 
            AND pm1.meta_value = pm2.meta_value
    )

It may also indicate that you cannot delete from tables that you use as select in this case. In this case try force the inner result as a temp table with a nested sub-query:

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 = '_email' 
    AND pm1.meta_key = pm2.meta_key 
    AND pm1.meta_value = pm2.meta_value
    AND p.ID IN (
      select t.ID from  (
        SELECT p.ID 
        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 = '_list' 
            AND pm1.meta_key = pm2.meta_key 
            AND pm1.meta_value = pm2.meta_value
        ) t 
    )

Answered By - ScaisEdge