Sql Update CPT from publish to draft and particular custom field

Question

I have:

CPT: match custom field: played

Is it possible by MySql to change the status from publish to draft of all posts of CPT 'match' with custom field set as '0'?

A good start can be

UPDATE wp_posts SET post_status = 'draft' WHERE post_type = 'match' AND meta_key = 'played' AND meta_value = 0;

but meta_key and meta_value are not in wp_posts table.

Thanks a lot for your support

Answer 1

You'll have to use JOIN to achieve this:

UPDATE wp_posts p INNER JOIN wp_postmeta pm ON p.ID = pm.post_id 
SET p.post_status = 'draft'
WHERE p.post_type = 'match' AND pm.meta_key = 'played' AND pm.meta_value = 0;