Sql Update CPT from publish to draft and particular custom field|Programmer puzzle solving

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

Share Improve this question edited Dec 5, 2018 at 19:06 Krzysiek Dróżdż 25.6k9 gold badges53 silver badges74 bronze badges asked Dec 5, 2018 at 17:47 Giulio 511 silver badge8 bronze badges

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;

Programmer puzzle solving

Sql Update CPT from publish to draft and particular custom field

1 Answer 1

Articles related to this article

comment list (0)