mysql - 从 wp_post 表中删除 ID

Question

我使用这个 SQL 查询来获取所有没有照片的帖子，它显示了 2010 年的帖子，但是使用什么 SQL 命令我可以删除那些 2010 年的帖子

SELECT * 
  FROM wp_posts 
  LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
   AND wp_postmeta.meta_key = '_thumbnail_id'
 WHERE 1 = 1 AND wp_postmeta.post_id IS NULL 
   AND wp_posts.post_type = 'post'
   AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
 GROUP BY wp_posts.ID 
 ORDER BY wp_posts.post_date

Answer 1

一种选择是使用EXISTS:

DELETE FROM wp_posts 
 WHERE EXISTS 
    (
    SELECT 1
      FROM wp_posts wp1
      LEFT JOIN wp_postmeta wp2 ON wp1.ID = wp2.post_id AND wp2.meta_key = '_thumbnail_id'
     WHERE wp2.post_id IS NULL
       AND wp1.post_type = 'post'
       AND (wp1.post_status = 'publish' OR wp1.post_status = 'private')
       AND wp1.ID = wp_posts.ID
     GROUP BY wp1.ID
     )  

或INNER JOIN:

DELETE w 
  FROM wp_posts w JOIN
    (
    SELECT wp1.ID
      FROM wp_posts wp1
      LEFT JOIN wp_postmeta wp2 ON wp1.ID = wp2.post_id AND wp2.meta_key = '_thumbnail_id'
     WHERE wp2.post_id IS NULL
       AND wp1.post_type = 'post'
       AND (wp1.post_status = 'publish' OR wp1.post_status = 'private')
     GROUP BY wp1.ID
     ) w2
     ON w.ID = w2.ID