1

I'm looking to write an SQL query to do the following:

Select all postmeta rows where the meta_value = a post id that does not exist.

For example, postmeta meta_value may equal 235. Usually this would relate to a post (attachment) with 235 as an ID, however, it has been deleted. I want to find any postmeta rows like this and remove them.

Hope this makes sense! Thanks in advance.

EDIT:

I've just found the NOT EXISTS function. I have a feeling something like this would work:

SELECT m.*
FROM wp_postmeta m
WHERE m.meta_key = 'image'
AND NOT EXISTS (SELECT *
    FROM wp_posts p
    WHERE m.meta_value = p.ID) 

Does this look right?

2 Answers 2

1

Of course, minutes after posting this I work it out. The following worked:

SELECT m.*
FROM wp_postmeta m
WHERE m.meta_key = 'image'
AND NOT EXISTS (SELECT *
    FROM wp_posts p
    WHERE m.meta_value = p.ID) 
0

SELECT * FROM wp_postmeta WHERE NOT EXISTS (SELECT * FROM wp_posts p WHERE wp_postmeta.post_id = p.ID)this will find all the broken metadata:

SELECT * FROM wp_postmeta WHERE NOT EXISTS (SELECT * FROM wp_posts p WHERE wp_postmeta.post_id = p.ID)

But you can probably find a plug-in to cleanup your database ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.