How to delete duplicate post_meta values in MySQL

Interesting approach here:

https://www.justin-cook.com/wp/2006/12/12/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!!!