SQL query to delete metadata of a custom post type, not the post

This is a really helpful thing to know how to do! And once you’re doing it, you’ll start to feel like you have super-powers. ??

SQL is a language that combines statements. So, to do this, you would break it down as:

1) Find the ID’s of all posts that are of type ‘deal’
2) Find all the postmeta entries where the meta key is ‘resort_chain’
3) Delete the postmeta entries that have the meta_key = resort chain, and a post_id from the first selection.

When you do this, make sure you do it on a development environment and PRACTICE it.

Here goes:

First step:
select id from wp_posts where post_type='deal'

Second step:
select * from wp_postmeta where meta_key='resort_chain'

Take a look at the post_id column form the second step, and note that these match some of the entries.

You can combine these queries like so:

select * from wp_posts wp, wp_postmeta wpm where wpm.meta_key='resort_chain' and wpm.post_id in (select wp.ID from wp_posts wp where wp.post_type='deal') and wp.ID = wpm.post_id

Make sure that this gets you the list of stuff you want to delete. You’ll see the post data as well as the post_meta data for the resort_chain.

Now, for the real test:

delete from wp_postmeta where meta_key='resort_chain' and post_id in (select id from wp_posts where post_type='deal')

Please note: there may be typos in the SQL. TEST IT FIRST.

That base query might work, but it will take a long time to execute. Here’s a query that will produce the same results and will execute perhaps two orders of magnitude faster:

SELECT * from wp_posts wp, wp_postmeta wpm
	WHERE wp.post_type = 'deal'
	AND wpm.post_id = wp.ID
	AND wpm.meta_key = 'resort_chain'

The order of the WHERE and AND clauses is important; do not change them. The corresponding DELETE would be something like:

DELETE FROM wp_postmeta wpm
INNER JOIN wp_posts wp ON (wp.ID = wpm.post_id)
	WHERE wp.post_type = 'deal'
	AND wpm.meta_key = 'resort_chain'

Please note that I haven’t tried these queries, so they might need some tweaking.

Thank you both so very much! I deeply appreciate the help.

I will copy my DB backup to a new (testing) db and test it all first. I’ll report back later today on the results.

1000 good karma points for each of you!!

Sorry it took me so long to post back (minor family emergency distracted me on friday and the weekend)….

Ancawonka’s method worked perfectly for both identifying the posts and deleting the associated metadata without deleting it from the other posts (thank you very much, Ancawonka!)

@diondesigns – your ‘select’ statement worked perfectly to help me identify the correct posts, but I got a SQL error on the ‘delete’ statement:

MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘wpm
INNER JOIN wp_posts wp ON (wp.ID = wpm.post_id)
WHERE wp.post_type = ‘dea’ at line 1

I copied and pasted it in, and made sure it said ‘deal’ for the post type, so not sure why it shows only ‘dea’ in the error statement, but then I don’t know much about SQL yet…….it could just be a matter of version incompatibility – I think I’m using MySQL version 5.1.

But I’m very grateful for your help!

Now, as is my custom, every time I post a question and get help, I spend an hour going through other posts to see if I can help someone else so I’m off to see who I can help! ??

Glad to be of assistance (and glad to see that you are paying it forward. ?? )

Sometimes the less efficient SQL is easier to read, write and understand correctly.