I recently needed to prune some duplicate rows from the WordPress post meta table. For reasons I won’t go into, there were two copies of many post meta values for most of the posts in the database and I wanted to remove the extra copies whilst keeping rest.
After trying various solutions (on a backup of the database) involving sub selects and unique keys, the simplest and fastest solution seems to be to create a new table and copy over rows I wanted to keep.
CREATE TABLE tmp_meta LIKE wp_postmeta AS SELECT MIN(meta_id) as meta_id, post_id, meta_key, meta_value FROM wp_postmeta GROUP BY post_id, meta_key, meta_value;
This creates a new table with the same specification as the
wp_postmeta table and inserts only one copy of each row based on the post_id, key and value. Then it’s just a question of swapping the names of the tables and testing the result.
DROP TABLE wp_postmeta; RENAME TABLE tmp_meta TO wp_postmeta;
Note that this will take the meta value with the lowest
meta_id and drop the rest. As always, take a backup of the database before doing this on your actual site.