how can I select the duplicate freelancer posts?

  post_id   meta_key                meta_value
  --------  -------------------     ----------------------------------------
  1         syndication_permalink   /
  2         syndication_permalink   /
  3         syndication_permalink   /
  4         syndication_permalink   
  5         syndication_permalink   

I actually want to change their status to draft

*UPDATE I ended up modifying some code and somehow got this working. I have had to hack together other bits of code, my SQL sux. But it works. I just got to figure out now how to do more than 'select' but also update post status to 'draft'. can't seem to figure out how to put an UPDATE on a SELECT query now

                    from wp_posts as bad_rows
                    LEFT JOIN wp_postmeta c ON ( bad_rows.ID = c.post_id )
                    inner join (
                    select post_title,id, MIN(id) as save_this_post_id
                    from wp_posts
                    inner JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
                    WHERE (
                    `post_status` = 'publish'
                    AND meta_key = 'syndication_permalink' 
                    )   
                    group by meta_value
                    having count(*) > 1
                    ) as good_rows on good_rows.post_title = bad_rows.post_title
                    and good_rows.save_this_post_id <> bad_rows.id
                    and (bad_rows.post_status='publish' AND meta_key = 'syndication_permalink' )
                    order by post_title,id;

how can I select the duplicate freelancer posts?

  post_id   meta_key                meta_value
  --------  -------------------     ----------------------------------------
  1         syndication_permalink   https://www.freelancer/projects/
  2         syndication_permalink   https://www.freelancer/projects/
  3         syndication_permalink   https://www.freelancer/projects/
  4         syndication_permalink   https://www.simplyhired/job/W6sVJ1
  5         syndication_permalink   https://www.mandy/uk/job/576913/junior

I actually want to change their status to draft

*UPDATE I ended up modifying some code and somehow got this working. I have had to hack together other bits of code, my SQL sux. But it works. I just got to figure out now how to do more than 'select' but also update post status to 'draft'. can't seem to figure out how to put an UPDATE on a SELECT query now

                    from wp_posts as bad_rows
                    LEFT JOIN wp_postmeta c ON ( bad_rows.ID = c.post_id )
                    inner join (
                    select post_title,id, MIN(id) as save_this_post_id
                    from wp_posts
                    inner JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
                    WHERE (
                    `post_status` = 'publish'
                    AND meta_key = 'syndication_permalink' 
                    )   
                    group by meta_value
                    having count(*) > 1
                    ) as good_rows on good_rows.post_title = bad_rows.post_title
                    and good_rows.save_this_post_id <> bad_rows.id
                    and (bad_rows.post_status='publish' AND meta_key = 'syndication_permalink' )
                    order by post_title,id;

• posts
• sql
• duplicates

• Make sure you check the post_type and post_status to make sure these duplicates aren't intentional. – MikeNGarrett Commented Jun 11, 2019 at 13:08

1 Answer 1

look at http://www.mysqltutorial/mysql-find-duplicate-values/

to get meta values, you need to assign :

wp_postmeta.meta_key = 'syndication_permalink' AND wp_postmeta.meta_value = '$yourvalue'