SQL query to remove duplicated postmeta from wordpress database

Question

I have some duplicated rows on my Wordpress wp_postmeta table. Actually hundreds of cases where old postmeta data are listed 2 or 3 times... maybe from some data import process done in the past... So I need to remove unneeded duplicate rows from wp_postmeta table, leaving just the ones with higher meta_id number... To exemplify what the wp_postmeta table looks like:

meta_id |   post_id   |  meta_key   |   meta_value
155153  |   177115    |  owner_img  |   https://www.example.com/a.jpg
176231  |   177115    |  owner_img  |   https://www.example.com/a.jpg
193983  |   177115    |  owner_img  |   https://www.example.com/a.jpg

Note that these are 3 metadata for the same post on wp_post table (as it has the same post_id)... so I just need to keep the latest metadata row, and delete all other instance where metadata is duplicated for each meta_key... how can I do that?

DELETE wp_postmeta.*
FROM wp_posts 
INNER JOIN wp_postmeta ON wp_postmeta.post_ID = wp_posts.ID 

Answer 1

I was able to figure it out after many research, in case anyone out there is looking for the answer...

DELETE t1 FROM wp_postmeta t1 
INNER JOIN wp_postmeta t2  
WHERE  t1.meta_id < t2.meta_id 
AND  t1.meta_key = t2.meta_key 
AND t1.post_id=t2.post_id;

Answer 2

You can do the deletion by first filtering out the lower valued meta_id

DELETE wp_posts 
FROM   wp_posts 
JOIN   wp_posts x ON x.post_ID = wp_posts.post_ID 
AND    wp_posts.meta_id < x.meta_id
WHERE  x.post_id=wp_posts.post_id
AND    x.meta_key = wp_posts.meta_key

exercise:

create table wp_posts (meta_id integer,
                   post_id   integer,
                   meta_key  varchar(20),
                   meta_value varchar(200)
                   );

 insert into wp_posts values(155153  ,   177115    ,  'owner_img','https://www.example.com/a.jpg');
 insert into wp_posts values(176231  ,  177115    ,  'owner_img' , 'https://www.example.com/a.jpg');
 insert into wp_posts values(193983  ,  177115    ,  'owner_img' ,  'https://www.example.com/a.jpg');
commit;

-- SELECT TO CHECK/VERIFY

Select  distinct wp_posts.meta_id
FROM   wp_posts 
JOIN   wp_posts x ON x.post_ID = wp_posts.post_ID 
AND    wp_posts.meta_id < x.meta_id
WHERE  x.post_id=wp_posts.post_id
AND    x.meta_key = wp_posts.meta_key

OUTPUT

meta_id
155153
176231

Delete

Delete  wp_posts
FROM   wp_posts 
JOIN   wp_posts x ON x.post_ID = wp_posts.post_ID 
AND    wp_posts.meta_id < x.meta_id
WHERE  x.post_id=wp_posts.post_id
AND    x.meta_key = wp_posts.meta_key

;
commit;
Select * from wp_posts;

OUTPUT

meta_id post_id meta_key    meta_value
193983  177115  owner_img   https://www.example.com/a.jpg