WordPress.org

1. granulr
   Member
   Posted 2 years ago #

   I recently used the wordpress export and import to move my posts to another blog. Unfortunately I noticed in the database that it had created 4 duplicates per post in the post_meta table.

   How do I erase duplicates in mysql?

2. MichaelH
   Volunteer
   Posted 2 years ago #

   Interesting approach here:

   http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/

   Backup your database! WordPress Backups

3. granulr
   Member
   Posted 2 years ago #

   Thanks MichaelH... But this query gets rid of all duplicates - I need to remove duplicates within all post_id.

   Basically all my posts have a number of custom field's (facitily_name, facitily_address, facility_state, facility_zip) and for some reason I have duplicates within a post_id. For example I have 4 records for the meta_key facility_name on every post_id.

4. MichaelH
   Volunteer
   Posted 2 years ago #

   Okay a little long winded but seems to work--using phpMyAdmin. Note my $table_prefix is wp30 so replace as appropriate:

   1. Browsing wp30_postmeta, use Operations to copy structure and data to wp30_postmetabu
   2. Drop wp30_postmeta
   3. Browsing wp30_postmetabu, use Operations to copy structure only to wp30_postmeta
   4. The in SQL use this:
   

   INSERT INTO
   wp30_postmetabu
   (
   post_id,
   meta_key,
   meta_value
   )
   SELECT post_id, meta_key, meta_value
   FROM wp30_postmeta
   GROUP BY concat(post_id, meta_key, meta_value)

   Of course backup ALL your tables before starting this.

5. wilonai10
   Member
   Posted 2 years ago #

   Hi people this is a quite informative post. I think from this post I could get some information which are very much valuable for mysql. And I think it will help me out for my problems also. Thank you so much for the post.

6. MichaelH
   Volunteer
   Posted 2 years ago #

   Actually I might have something backward. Let me try it and I may modify that...

7. granulr
   Member
   Posted 2 years ago #

   I ran the above code and it seemed to work great...

   I just need to check and make sure everything is there.

8. MichaelH
   Volunteer
   Posted 2 years ago #

   Jeesh, I had this backward ..
   

   INSERT INTO
   wp30_postmeta
   (
   post_id,
   meta_key,
   meta_value
   )
   SELECT post_id, meta_key, meta_value
   FROM wp30_postmetabu
   GROUP BY concat(post_id, meta_key, meta_value)

9. granulr
   Member
   Posted 2 years ago #

   Thanks MichaelH this worked perfectly!!!

Topic Closed

This topic has been closed to new replies.