Q:

Update table based on another table with conditions

I want to update all columns where the post_type = product and the post_status = publish. Furthermore I need to make a join because I want to update only those posts, where also the Count(...) is 0.
I want to count the meta meta_key LIKE "%product_shops_%_price%".
Here are two examples of the tables:
wp441_posts:
| ID | post_status | post_type |
--------------------------------
|  1 |   publish   |  product  |
|  2 |    darf     |  product  |
|  3 |    darf     |  product  |
|  4 |   publish   |    page   |
|  5 |   publish   |    page   |
|  6 |   publish   |    page   |
--------------------------------

wp441_postmeta:
| meta_id | post_id |        meta_key         | met_value |
----------------------------------------------------------
|  1      |    1    |  product_shops_0_price  |    45.00  
|  2      |    1    |  product_shops_1_price  |    25.00  |
|  3      |    1    |  product_shops_2_price  |    45.00  |
|  4      |    2    |       thumbnail_url     | https://..|
-----------------------------------------------------------

For example. In the two tables I only want to set the post_status of the ID 1 to draft because this is the only post where my conditions are matching. I have more then 0 product_shops_%_price prices in the wp441_postmeta table, I have a post which is on publish at the moment and it is a `product``.
I already tinkered a statement but this one is without join at the moment:
SELECT COUNT(meta_key) FROM `wp441_postmeta` WHERE meta_key LIKE "%product_shops_%_price%" GROUP BY (post_id) 

Now I need to insert the join in this statement. But I have no idea how... Can somebody help me please?
Greetings!

A:

For your explained case to update wp441_posts.post_status to value draft where:

post_type = 'product AND post_status = 'public' 
and matching post_id with wp441_postmeta.post_id having more than 1 row where meta_key LIKE '%product_shops_%_price%'

the statement would look like this:
UPDATE wp441_posts p
JOIN (
  SELECT pm.post_id
  FROM wp441_postmeta
  WHERE meta_key LIKE '%product_shops_%_price%'
  GROUP BY pm.post_id
  HAVING COUNT(*) > 0
  ) pm
  ON p.id = pm.post_id
SET post_status = 'draft'
WHERE pm.post_type = 'product' AND pm.post_status = 'publish'

I think your LIKE should compare with product_shops_%_price without additional % sign at the beggining and end of the string, but that's specific to how WordPress puts values in there. Consider it though.

