如何转换此SQL查询以删除特定meta_value而不是post_title的重复帖子？ [英] How can I convert this SQL query to delete duplicate posts by specific meta_value instead of post_title?

问题描述

此查询的工作原理与post_title为我的自定义帖子类型删除所有重复的帖子完全相同：

This query works exactly as it should to DELETE all duplicate posts by post_title for my custom post type:

$sql = "
    DELETE a.* 
    FROM wp_posts 
    AS a 
    INNER JOIN ( 
        SELECT post_title, MIN( id ) 
        AS min_id 
        FROM wp_posts 
        WHERE post_type = 'banners' 
        AND post_status = 'publish' 
        GROUP BY post_title HAVING COUNT( * ) > 1  ) 
    AS b 
    ON b.post_title = a.post_title 
    AND b.min_id <> a.id 
    AND a.post_type = 'banners' 
    AND a.post_status = 'publish'";

$result = $conn->query($sql);

我不能为我的生活弄清楚如何根据特定的meta_value而不是post_title来调整这个来重复删除重复项，而不必加入wp_postmeta表。这是尝试修改此内容的多次迭代中的最后一次：

I can't for the life of me figure out how to adapt this to DELETE duplicates based on a specific meta_value instead of a post_title with having to JOIN the wp_postmeta table. This is the last of many iterations attempting to modify this:

$sql1 = "
    DELETE p, pm 
    FROM wp_posts p 
    INNER JOIN wp_postmeta pm 
    ON p.ID=pm.post_ID 
    WHERE p.post_type='banners' 
    AND p.post_status='publish' 
    AND pm.meta_key='uselink' 
    AND pm.meta_value='example.com' 
    AND (
        SELECT count(pm.meta_value) 
        FROM wp_postmeta pm 
        GROUP BY pm.meta_value 
        HAVING COUNT(*) > 1)'";

$result1 = $conn->query($sql1);

最近更新的修改尝试（谢谢你的帮助），仍然没有做到正确。我很确定我想要删除关联的帖子，然后运行清理功能来删除孤立的元数据。我仍然坚持如何让它删除正确的帖子。使用meta_id作为列键不会发生任何事情，当然使用post_id会导致它们全部（不仅仅是重复）被删除。

A recently updated attempt to modify (thank you for your help), still not getting it right. I'm pretty sure I want to delete the associated post, then run a cleanup function to remove the orphaned meta data. I'm still stuck on how to get it to delete the right posts. Using the meta_id as the column key causes nothing to happen, and of course using post_id causes them all (not just duplicates) to be deleted.

$sql1 = "
    DELETE p 
    FROM wp_posts p 
    INNER JOIN wp_postmeta pm 
    ON p.ID=pm.post_ID 
    WHERE p.post_type='dillonbros-banners' 
    AND p.post_status='publish' 
    AND pm.meta_key='uselink' 
    AND pm.meta_value='http://www.dillon-brothers.com/throttlethursday/' 
    AND NOT EXISTS ( 
        SELECT 1 
        FROM wp_postmeta pm2 
        WHERE pm2.post_id = p.Id 
        AND pm2.meta_key = pm.meta_key 
        AND pm2.meta_value = pm.meta_value 
        AND pm2.meta_id > pm.meta_id)";

wp_posts列： id，post_author，post_date，post_date_gmt，post_content，post_title，post_excerpt，post_status，comment_status，ping_status，post_password，post_name，to_ping，pinged， post_modified，post_modified_gmt，post_content _filtered，post_parent，guid，menu_order，post_type，post_mime_type，comment_count



wp_postmeta列： meta_id（自动增量），post_id，meta_key，meta_value



重要： wp_posts id = wp_postmeta post_id

wp_posts columns: id, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count

wp_postmeta columns: meta_id (auto increment), post_id, meta_key, meta_value

important: wp_posts id = wp_postmeta post_id

推荐答案

sql =
DELETE a。*
FROM wp_posts
AS a
INNER JOIN（
SELECT post_title，MIN（id）
AS min_id
FROM wp_posts
WHERE post_type ='banners'
AND post_status ='publish'
GROUP BY post_title HAVING COUNT（*）> 1）
AS b
ON b.post_title = a.post_title
AND b.min_id<> a.id
AND a.post_type ='banners'
AND a.post_status ='publish';

sql = " DELETE a.* FROM wp_posts AS a INNER JOIN ( SELECT post_title, MIN( id ) AS min_id FROM wp_posts WHERE post_type = 'banners' AND post_status = 'publish' GROUP BY post_title HAVING COUNT( * ) > 1 ) AS b ON b.post_title = a.post_title AND b.min_id <> a.id AND a.post_type = 'banners' AND a.post_status = 'publish'";

result =

result =

conn-> query（

conn->query(

这篇关于如何转换此SQL查询以删除特定meta_value而不是post_title的重复帖子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！