WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] How to delete duplicate post_meta values in MySQL

[Resolved] How to delete duplicate post_meta values in MySQL

  • 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?

Viewing 8 replies - 1 through 8 (of 8 total)
  • Interesting approach here:

    Remove duplicate entries / rows a mySQL database table

    Backup your database! WordPress Backups

    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.

    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.

    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.

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

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

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

    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)

    Thanks MichaelH this worked perfectly!!!

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘[Resolved] How to delete duplicate post_meta values in MySQL’ is closed to new replies.