How can I convert this SQL query to delete duplicate posts by specific meta_value instead of post_title?

Question

0.00/5 (No votes)

See more:

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

PHP

$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);

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:

PHP

$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);

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.

PHP

$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 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

Posted 15-Jul-15 12:48pm

Member 11840232

Updated 16-Jul-15 9:18am

v4

Add a Solution

Comments

Sergey Alexandrovich Kryukov 15-Jul-15 19:00pm

I wonder, why not blocking duplicate posts in first place?
—SA

Wendelius 15-Jul-15 23:33pm

Agree, I added the comment to the answer.

_Asif_ 16-Jul-15 1:08am

Can you post your wp_posts and wp_postmeta table structures especially how they linked together (FKs?). If you add some sample data that you want to delete from wp_posts would also help us in giving you good solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-15T17:32:00

In order to figure out the correct SQL statement we'd need to know the tables structure and the meaning of the columns and so on. Without knowing all the logic only some guidelines can be explained.

Having that said, one thing I didn't quite understand was, are you trying to delete from wp_posts or from wp_postmeta. Based on the last condition I take it you're deleting from wp_postmeta.

The next thing is that there should be some kind of key in the wp_postmeta table. Something to identify the row. Since this is unknown, I call it a KeyCol in the query. You should replace it with the correct column name.

BAsed on those the statement for deletion could look something like

SQL

DELETE pm 
    FROM wp_postmeta pm
    INNER JOIN wp_posts p 
    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 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.KeyCol > pm.KeyCol) <-- delete only previous rows

However, as Sergey Alexandrovich Kryukov[^] pointed out in the comment, you should have a natural key present in the table. Taken that I have used the correct column in the delete query you could have a unique constraint on the table for columns:
- post_id
- meta_key
- meta_value
This would eliminate the possibility for having duplicates but the logic must apply for all post types. If that is not the case then consider checking the duplicates in an INSERT trigger and preventing them over there.

The last thing is that if you don't have a key column in the table and you can't add one for some reason, you still need to uniquely identify a row. One way is to use the physical location of the row as explained in Physical location of a row in SQL Server[^]