How to update pm_virtual.meta_value = 'no' in Woocommerce

December 7, 2023

I need to update the value of pm_virtual.meta_value = 'no', only for those products that have pm_downloadable.meta_value = 'yes'. How do I do this?

SELECT
  p.ID,
  p.post_title,
  pm_downloadable.meta_value AS downloadable_trait,
  pm_virtual.meta_value AS virtual_trait
FROM
  xhdps_posts AS p
  INNER JOIN xhdps_postmeta AS pm_downloadable ON p.ID = pm_downloadable.post_id
  LEFT JOIN xhdps_postmeta AS pm_virtual ON p.ID = pm_virtual.post_id
  AND pm_virtual.meta_key = '_virtual'
WHERE
  p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm_downloadable.meta_key = '_downloadable'
  AND pm_downloadable.meta_value = 'yes'
  AND pm_virtual.meta_value = 'no'

>Solution :

You can use an UPDATE`` statement along with a JOIN` to filter the products based on the specified conditions.

UPDATE xhdps_postmeta AS pm_virtual
INNER JOIN xhdps_postmeta AS pm_downloadable ON pm_virtual.post_id = pm_downloadable.post_id
INNER JOIN xhdps_posts AS p ON pm_downloadable.post_id = p.ID
SET pm_virtual.meta_value = 'no'
WHERE 
    p.post_type = 'product'
    AND p.post_status = 'publish'
    AND pm_downloadable.meta_key = '_downloadable'
    AND pm_downloadable.meta_value = 'yes'
    AND pm_virtual.meta_key = '_virtual';