Mysql delete similar post : can't specify target table 'wp_posts' for update in FROM clause

Question

After an importing error, there is many duplicate content from my posts.

Then, I try to delete this posts with that query :

DELETE 
FROM wp_posts USING wp_posts
LEFT JOIN wp_postmeta pm ON wp_posts.ID = pm.post_id 
AND pm.meta_key="_wpbdp[fields][6]"
LEFT JOIN wp_wpbdp_listing_fees wlf ON wp_posts.ID = wlf.listing_id 
WHERE wp_posts.post_type="wpbdp_listing"
AND wp_posts.post_status="publish"
AND EXISTS (
    SELECT NULL
    FROM wp_posts p2
    LEFT JOIN wp_postmeta pm2 ON p2.ID = pm2.post_id 
    AND pm2.meta_key="_wpbdp[fields][6]"
    LEFT JOIN wp_wpbdp_listing_fees wlf2 ON p2.ID = wlf2.listing_id 
    WHERE p2.post_type="wpbdp_listing" 
    AND pm2.meta_value=pm.meta_value
    AND p2.post_status="publish"
    AND wlf2.category_id=wlf.category_id
)

Unfortunately, I can't do a SELECT statement who calls the same table that I want to delete. Is there another solution ?

Do you have some sort of ID or something? If so, do a select getting the duplicate IDs, store it in an array and then delete the IDs in a second attempt. Alternatively: How do you determine that a row is 'duplicate'? A 'UNIQUE' key might well solve your problem then. — ATaylor, Dec 27 '12 at 08:39
you can read there http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Nick, Dec 27 '12 at 08:39
I Think you need to write a script and run several querys to clean your database. — m4t1t0, Dec 27 '12 at 08:48
Yep, I can sort by ID, but there is no duplicate IDs. The only way to discover a duplicate content is to check in the `wp_postmeta` if the post have same city (`_wpbdp[fields][6]`) and the same category (`wp_wpbdp_listing_fees.category_id`). Then, I check your link @ATaylor, and i don't know how to proceed ... Can you help me ? ^^ — Florian Mithieux, Dec 27 '12 at 09:54
I didn't post any link, really. That was 'Nick'. Anyway, how about running (Pseudo-Code): `ALTER IGNORE TABLE ADD UNIQUE (city, category);` This will add a 'unique index' to your table (please look at `Alter Table` in the documentation for the exact query and drop ALL duplicates. — ATaylor, Dec 27 '12 at 10:24
Oops sorry, that was 'Nick' indeed. I can't make 'city' and 'category' unique, because they are starred in another table and many posts have the same city for exemple, and it isn't a duplicate entry.. — Florian Mithieux, Dec 27 '12 at 10:32

score 1 · Accepted Answer · answered Dec 28 '12 at 13:43

I tried something new, and it passed. Following the @Nick's idea, I did that query :

DELETE p
FROM wp_posts p, wp_posts p2
WHERE p.post_title = p2.post_title
AND p.post_type="wpbdp_listing" 
AND p2.post_type="wpbdp_listing" 
AND p2.post_status="publish"
AND p.post_status="publish"
AND EXISTS(
    SELECT NULL
    FROM wp_wpbdp_listing_fees wlf 
    WHERE p.ID = wlf.listing_id
    AND EXISTS (
        SELECT NULL
        FROM wp_wpbdp_listing_fees wlf2 
        WHERE wlf2.category_id = wlf.category_id
        AND p.ID = wlf2.listing_id
    )
)
AND EXISTS(
    SELECT NULL
    FROM wp_postmeta pm 
    WHERE p.ID = pm.post_id
    AND pm.meta_key="_wpbdp[fields][6]"
    AND EXISTS (
        SELECT NULL
        FROM wp_postmeta pm2 
        WHERE pm2.meta_key="_wpbdp[fields][6]"
        AND p.ID = pm2.post_id
        AND pm2.meta_value=pm.meta_value
    )
)
AND p.ID < p2.ID

It's very ugly and not optimised, but it works !

By the way, thanks for your answers !

Mysql delete similar post : can't specify target table 'wp_posts' for update in FROM clause

1 Answers1