Setting product visibility via SQL (WordPress + WooCommerce)

spark_sara

The following problem can be considered as a special case of adding categories to posts by SQL query [1], but answers relating to that general case are highly welcome too: Let’s say we have 10.000 products and want to set their product visibility to “Hidden” (as if done 10.000 times, once for each product, in WP-admin). How can this be done safely with (which) SQL query? I have some suggestions that seem to work (in practice, so far), based on...

Originally asked by Roger Grading on Stack Exchange (stackoverflow) (CC BY-SA 4.0).

uphilltech

Great question — answering as the UphillTech team:

The SQL queries you've suggested are generally correct for setting the product visibility to "Hidden" by manipulating the wp_term_relationships and wp_term_taxonomy tables. Here's a refined version of your queries: exclude-from-search: INSERT INTO wp_term_relationships (object_id, term_taxonomy_id) SELECT ID, (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'product_visibility' AND term_id = (SELECT term_id FROM wp_terms WHERE slug = 'exclude-from-search')) FROM wp_posts WHERE post_type = 'product' AND post_status = 'publish'; **exclude-from-catalog:** **INSERT INTO wp_term_relationships (object_id, term_taxonomy_id) SELECT ID, (SELECT term_taxonomy_id FROM wp_term_taxonomy...

Full answer by Rafael Minuesa: https://stackoverflow.stackexchange.com/a/78408778 (CC BY-SA 4.0).

Need it done for your business? UphillTech.