How to search two tables?

Question

SQL

There are 2 tables: both wp_posts and wp_reviews.


Please tell me how to update the post status in the wp_posts (`post_status`) table to 'draft' for those posts that have no rows in the wp_reviews table. wp_posts `ID` is equal to wp_reviews `company_id`

i.e. if the record with company_id=2001 does not exist in the wp_reviews table, then for the wp_posts table for the row with ID=2001 we update post_status to 'draft'

Answer 1

those. if the record with company_id=2001 does not exist in the wp_reviews table, then for the wp_posts table for the row with ID=2001 we update post_status to 'draft'

As it were, you already have 80% of the query formed in a natural language suitable for direct translation into SQL. Somehow it is a shame not to write it down in plain text:

update wp_posts p
set p.post_status = 'draft'
where not exists (select 1 from wp_reviews r where r.связующий_ключ = p.связующий_ключ)

It remains to decide how the keys are called in one and the other table, linking the data of these tables.
PS: probably this: r.company_id = p.id.