Leave oldest duplicate

Databases
1

[QUOTE=maestro360;4995609]Yep, that’s a lot of duplicates.

Thank you both for your answers. r937, you’ve came through for me again. That’s a very interesting query. Creating a temp table to call a join on is something I haven’t seen before.

I can generate now a list of meta id’s for my dupes. I’ve tried altering the SQL to generate only the id’s then wrap it in its own subquery and delete everything within the list it generates.


DELETE wp_postmeta FROM wp_postmeta where meta_id in(
  SELECT t.meta_id
  FROM ( SELECT post_id
              , meta_key
           FROM wp_postmeta
         GROUP
             BY post_id
              , meta_key
         HAVING COUNT(*) > 1 ) AS dupes
INNER
  JOIN wp_postmeta AS t
    ON t.post_id  = dupes.post_id
   AND t.meta_key = dupes.meta_key
)

I get an alert saying I can’t specify the target table for update in a where clause. I’ve found this is because MySQL won’t update the same table that’s been used in a the SELECT part of the query.

Any suggestions on how to work around this?[/QUOTE]

I have a similar problem instead i have data from a jobmanager plug in, where each candidates resume is in wp_postmeta as each row , and have tons of duplicates f.e each candidates has wp_posts row and also about 8 rows for each post_id, however i don’t want to delete all the duplicates i want to leave one ( the oldest one), can someone help.

Thanks
Fuad