Question 300088 in Wordpress Question posted in
The official Wordpress documentation can be found here.

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

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'
Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    Just break the query to take the XHDPS_POSTMETA table out of the SELECT query and UPDATE it with relevant values as follows:

    UPDATE XHDPS_POSTMETA
   SET
    META_VALUE = 'no'
WHERE meta_value != 'no'
  AND meta_key = '_virtual' 
  AND post_id IN (
SELECT p.ID
  FROM xhdps_posts AS p
  INNER JOIN xhdps_postmeta AS pm_downloadable ON p.ID = pm_downloadable.post_id
 WHERE     P.POST_TYPE = 'product'
       AND P.POST_STATUS = 'publish'
       AND PM_DOWNLOADABLE.META_KEY = '_downloadable'
       AND PM_DOWNLOADABLE.META_VALUE = 'yes');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search