CODEWITHSUNDEEP
phpmysqlwordpresswoocommerce
Malav Vasita
Malav Vasita

Reputation: 130

MySQL query for getting WooCommerce products by category name

I want to update products by specific category only.

I am getting correct results by this MySQL query:

SELECT * FROM wp_posts 
WHERE post_type = 'product'

Now, I have 2 types of product categories 1. ABC and 2. XYZ ABC has 5 products and XYZ has 30000 products.

I just want to update the meta data of products having categoty ABC

For that I have written MySQL Query:

UPDATE `wp_postmeta`
SET `meta_value`= ( CASE WHEN `meta_key` = '_stock' THEN '0'
                         WHEN `meta_key` = '_stock_status' THEN 'outofstock'
                         ELSE `meta_value`
                    END)
WHERE post_id NOT IN( '2268', '2298', '2299', '2135' );

I do not want to use NOT IN because if any category will be added, then this query will be wrong.

How can I check for the product category in this MySQL query?

Thanks in Advance.

Upvotes: 2

Views: 898

Answers (1)

Christian Baumann
Christian Baumann

Reputation: 3435

A subquery should do:

UPDATE `wp_postmeta` 
SET    `meta_value`= ( 
       CASE 
              WHEN `meta_key` = '_stock' THEN '0' 
              WHEN `meta_key` = '_stock_status' THEN 'outofstock' 
              ELSE `meta_value` 
       end) 
WHERE  post_id NOT IN 
       ( 
              SELECT id
              from   wp_posts 
              WHERE  post_type = 'product' );

Upvotes: 1

Related Questions