WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How to delete duplicate post_meta values in MySQL (9 posts)

  1. granulr
    Member
    Posted 4 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
    Member
    Posted 4 years ago #

  3. granulr
    Member
    Posted 4 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
    Member
    Posted 4 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 4 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
    Member
    Posted 4 years ago #

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

  7. granulr
    Member
    Posted 4 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
    Member
    Posted 4 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 4 years ago #

    Thanks MichaelH this worked perfectly!!!

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags