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.
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?