How to find meta entries with their posts missing via SQL?

Question

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?

Answer 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) 

Answer 2

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 ;-)