How to modify this working SQL SELECT/JOIN query to DELETE the duplicates?

Question

I'm having trouble modifying this to delete the duplicate records. In this case, I had three posts with the same meta_value for the given meta_key and it returned two results (I may have a problem with the GROUP BY--still working it out). However changing the first SELECT to DELETE does not work. Isn't this the part I would change so data is deleted from both tables?

$sql1 = "SELECT * 
         FROM (
             SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_type, wp_posts.post_status, wp_postmeta.meta_value 
             FROM wp_posts, wp_postmeta 
             WHERE wp_posts.ID=wp_postmeta.post_ID 
             AND post_type='banners' 
             AND post_status='publish' 
             AND meta_key='uselink' 
             AND meta_value='http://www.mylink.com/subpage/') 
         AS a 
         INNER JOIN ( 
             SELECT meta_value, MIN( post_id ) 
             AS min_id 
             FROM wp_postmeta 
             GROUP BY meta_value 
             HAVING COUNT( * ) > 1  ) 
         AS b 
         ON b.meta_value = a.meta_value 
         AND b.min_id <> a.id";

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

if ($result1->num_rows > 0) {
    while($row = $result1->fetch_assoc()) {
        echo $row["meta_value"] . ", ";
    }
} else {
    echo "0 results";
}

This is what I tried (not working):

$sql1 = "DELETE * 
         FROM (
             SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_type, wp_posts.post_status, wp_postmeta.meta_value 
             FROM wp_posts, wp_postmeta 
             WHERE wp_posts.ID=wp_postmeta.post_ID 
             AND post_type='banners' 
             AND post_status='publish' 
             AND meta_key='uselink' 
             AND meta_value='http://www.mylink.com/subpage/') 
        AS a 
        INNER JOIN ( 
             SELECT meta_value, MIN( post_id ) 
             AS min_id FROM wp_postmeta 
             GROUP BY meta_value HAVING COUNT( * ) > 1  ) 
        AS b 
        ON b.meta_value = a.meta_value 
        AND b.min_id <> a.id";

I'm going off this one without a second joined table that works:

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'

the difference between select and delete query is `SELECT * FROM table WHERE this=that` and `Delete FROM table WHERE this=that`, so you see replacing just delete with select won't work — Shehary, Jul 15 '15 at 17:11
I added to the question just to help me lay this out in my head. I'm just unclear on how to handle if because with only one table just changing that first SELECT to DELETE did work; I don't know if it has to do with the fact there are two tables? — WorkingMan8798, Jul 15 '15 at 17:13
Does the DELETE need to come after I join the tables? Is that the issue? — WorkingMan8798, Jul 15 '15 at 17:22
First thing you have to remove `*` from your DELETE QUERY, It should look like `DELETE FROM` not `DELETE * FROM` 2nd you can not SELECT in InnerJoin, just join the table with WHERE Clause and good to go — Shehary, Jul 15 '15 at 17:22
possible duplicate of [SQL DELETE with INNER JOIN](http://stackoverflow.com/questions/8598791/sql-delete-with-inner-join) — Shehary, Jul 15 '15 at 17:24
Please be logical, one hand you are trying to Delete and 2nd hand you are USING SELECT, You Don't need to SELECT, e.g `$sql1 = "DELETE FROM wp_posts WHERE wp_posts.ID=wp_postmeta.post_ID` — Shehary, Jul 15 '15 at 17:27
Thank you for the guidance; I wasn't aware of the join issue. Also, I didn't see that question before; thanks for pointing it out for reference. — WorkingMan8798, Jul 15 '15 at 17:27
Absolutely stuck; tried so many iterations of the DELETE from wp_posts WHERE — WorkingMan8798, Jul 15 '15 at 19:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83364/discussion-between-shehary-and-deez717). — Shehary, Jul 15 '15 at 19:08

Shehary · Answer 1 · 2015-07-15T20:53:27.063

0

Try This, (But I'm not sure)

$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='link'
AND (SELECT count(pm.meta_value) FROM
wp_postmeta pm
GROUP BY pm.meta_value
HAVING COUNT(*) > 1
WHERE pm.meta_value=p.meta_value)";

edited Jul 15 '15 at 20:53

answered Jul 15 '15 at 19:48

Shehary

9,926
10
42
71

I appreciate the follow up; I can follow that bit of logic I think. It's the duplicate count check that I must be misunderstanding. I want to find and delete those duplicates. The select returned them, but I just can't grasp how to integrate that INNER JOIN/HAVING COUNT part – WorkingMan8798 Jul 15 '15 at 20:14
for instance with all the different iterations I've been trying this does delete the posts per the WHERE condition but now I need it to only delete the duplicates: $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='http://www.mylink.com/subpage/'"; – WorkingMan8798 Jul 15 '15 at 20:16
Hey sorry for not checking back; I'm still working on it...5 days and counting...thank you for the guidance--gotta be getting close – WorkingMan8798 Jul 15 '15 at 22:07

How to modify this working SQL SELECT/JOIN query to DELETE the duplicates?

1 Answers1