mysql删除类似post : can't specify target table 'wp_posts' for update in FROM clause

Question

导入错误后，我的帖子中有很多重复的内容。

然后，我尝试使用该查询删除此帖子:

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
)

不幸的是，我无法执行调用我要删除的同一个表的 SELECT 语句。还有其他解决方案吗？

Answer 1

我尝试了一些新的东西，它通过了。按照@Nick 的想法，我做了那个查询:

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

它非常丑陋且未优化，但它有效!

顺便说一句，谢谢您的回答!