mysql - 从 wp_post 表中删除一个 ID
问题描述
我使用这个 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
解决方案
一种选择是使用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
推荐阅读
- spring-boot - 强制验证注解提供消息
- visual-studio-code - Visual Studio Code,如何将文件或文件夹从一个分支复制到另一个分支?
- apache-flink - 当输入文件很大时,AWS EMR 上的 Flink akka AskTimeoutException
- java - JDK 17:Switch 语句导致 java.lang.VerifyError:操作数堆栈上的类型错误
- javascript - 我无法将 firebase 连接到我的 html 文件
- php - 如何在 laravel 任务调度程序中运行 queue:work 命令?
- python - 根据条件删除相邻的列表元素?
- triggers - EEG BIOSEMI 引发精神病
- domain-driven-design - 更新 DDD 中的子实体
- laravel - laravel cast 如何在幕后工作