Sql Update CPT from publish to draft and particular custom field

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

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

• custom-post-types
• custom-field
• mysql
• post-status

Share Improve this question edited Dec 5, 2018 at 19:06 Krzysiek Dróżdż 25.6k99 gold badges5353 silver badges7474 bronze badges asked Dec 5, 2018 at 17:47 GiulioGiulio 5111 silver badge88 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;