The WooCommerce Wordpress plugin doesn't have a way to update all prices for all products and all variations in bulk. You have to do it by hand, in the Wordpress admin editor, or pay for a "bulk edit" plugin to do it.
Or, you can hack the database directly using some fancy SQL. It took me a while to figure this out.
-- Update all prices for all variations and products that are 4.99, change to 5.99
update `wp_wc_product_meta_lookup`
set min_price = 5.99
where
min_price = 4.99
update `wp_wc_product_meta_lookup`
set max_price = 5.99
WHERE
max_price = 4.99
-- for some reason changing the above doesn't actually affect the live site. you also have to update this table which apparently is the one that matters?
update `wp_postmeta`
set meta_value = '5.99'
where (meta_key = '_regular_price' or meta_key = '_price')
and meta_value = '4.99'
-- clear cached prices (you can probably just delete these, but i chose to back them up because i have no idea what i'm doing!)
update `wp_options` set option_name = REPLACE(option_name, '_transient', '_BACKUP_transient') where option_name like '_transient_wc_var_prices%'For more details, here's what ChatGPT had to say:
what is the relationship in woocommerce between the wp_wc_product_meta_lookup and wp_postmeta tables?
ChatGPT The wp_wc_product_meta_lookup table and wp_postmeta table in WooCommerce are related, and they both store metadata for products. However, they serve different purposes and have distinct structures.
wp_wc_product_meta_lookup Table:
This table is specifically designed for performance optimization in WooCommerce. It contains pre-aggregated data related to products, making it more efficient for querying and retrieving product metadata. It includes columns such as product_id, sku, virtual, downloadable, min_price, max_price, onsale, and others. The data in this table is aggregated and updated periodically, improving the speed of certain types of queries related to product information. wp_postmeta Table:
The wp_postmeta table is a core WordPress table used to store metadata for various post types, including products in WooCommerce. It contains a broader range of metadata for posts, pages, and custom post types, not just limited to products. The structure includes columns such as meta_id, post_id, meta_key, and meta_value. WooCommerce uses this table to store specific metadata related to products, variations, orders, and other elements. Relationship:
The wp_wc_product_meta_lookup table is designed to improve the performance of queries related to product metadata by pre-aggregating certain data. The data in wp_wc_product_meta_lookup is derived from the wp_postmeta table but is organized in a way that facilitates faster retrieval for specific queries. The product_id column in wp_wc_product_meta_lookup corresponds to the post_id column in wp_postmeta, establishing the relationship between the two tables. In summary, while both tables store metadata for WooCommerce products, wp_wc_product_meta_lookup is a performance-optimized table that contains pre-aggregated data for faster queries, and wp_postmeta is a more general table used for storing various types of metadata across the WordPress site.
And here's what it had to say on transient options:
what is transient_wc_var_prices in woocommerce ChatGPT In WooCommerce, the transient_wc_var_prices is a transient key used to store and retrieve transient data related to variable product prices. Transients are a way to cache and temporarily store data in WordPress for a defined period, reducing the need to repeatedly query the database for the same information.
For variable products in WooCommerce, pricing can be dynamic based on variations, and calculating prices on-the-fly for each request can be resource-intensive. To optimize performance, WooCommerce uses transients to store and retrieve pre-calculated pricing information for variable products.
Here's how it generally works:
Transient Key: transient_wc_var_prices is the base key used for these transients. It is usually followed by a hash or unique identifier that corresponds to the specific set of variations or product for which the transient is stored.
Contents of the Transient: The transient data typically includes pre-calculated minimum and maximum prices for variable products, considering all the variations. This helps WooCommerce avoid recalculating prices on each page load and improves the speed of displaying variable product information.
Expiration: Transients have an expiration time, after which they are considered stale. WooCommerce sets an expiration time for these transients to ensure that the pricing information is updated periodically.
For example, you might find a transient key like _transient_wc_var_prices_123456 where 123456 is a unique identifier for a specific set of variations or product.
It's important to note that transients are a form of caching, and they are meant to enhance performance by reducing the need for repeated calculations. If you encounter issues with variable product prices not updating as expected, clearing these transients might help. However, it's generally not recommended to manually manipulate transients unless you fully understand the consequences and are aware of the potential impact on performance.
SELECT * FROM `wp_posts` as variations
left join `wp_posts` as parents on parents.post_type = 'product' and parents.id = variations.post_parent
where variations.post_type like 'product_variation' and variations.post_status = 'publish'
ORDER BY variations.`post_type` DESC
SELECT * FROM `wp_wc_product_meta_lookup` as meta
inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product'
SELECT * FROM `wp_wc_product_meta_lookup` as meta inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product_variation'
SELECT * FROM `wp_wc_product_meta_lookup` as meta
inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product'
ORDER BY `meta`.`min_price` ASC
SELECT * FROM `wp_wc_product_meta_lookup` as meta
inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product'
WHERE min_price between 3 and 5
ORDER BY `meta`.`min_price` ASC
SELECT * FROM `wp_wc_product_meta_lookup` as meta
inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product_variation'
WHERE min_price between 3 and 5 and posts.post_parent = 86844
SELECT * FROM `wp_wc_product_meta_lookup`
where sku like 'vp102' or product_id in (83016, 82718, 86844)
SELECT * FROM `wp_wc_product_meta_lookup` as meta
inner join `wp_posts` as posts on meta.product_id = posts.id and posts.post_type like 'product%'
WHERE posts.post_parent in (83016, 82718, 86844)
where product_id = 86844
SELECT * FROM `wp_wc_product_meta_lookup` as meta
where product_id IN (86844, 87146, 87147)
AND
min_price between 3 and 5
update `wp_wc_product_meta_lookup`
set min_price = 5.9900
where product_id IN (86844, 87146, 87147)
AND
min_price = 4.99
update `wp_wc_product_meta_lookup`
set max_price = 5.9900
where product_id IN (86844, 87146, 87147)
AND
max_price 4.99
update `wp_postmeta`
set meta_value = '5.99'
where (meta_key = '_regular_price' or meta_key = '_price')
and meta_value = '4.99'
and post_id = 87147
SELECT * FROM `wp_postmeta` where post_id = 86847 or post_id in (Select id from `wp_posts` as p where p.post_parent = 86847)
SELECT * FROM `wp_options` where option_name like '_transient_wc_var_prices%'
SELECT * FROM `wp_postmeta`
where (meta_key = '_regular_price' or meta_key = '_price')
and post_id = 87147
update `wp_postmeta`
set meta_value = '4.97'
where (meta_key = '_regular_price' or meta_key = '_price')
and meta_value = '4.98'
and post_id = 87147