Trying to locate and bulk edit WooCommerce prices in MySQL
commerce, mysql, woocommerce, wordpress
Solution
This is how you would print the prices:
SELECT p.id, p.post_title, m.meta_key, m.meta_value
FROM wp_posts p
INNER JOIN wp_postmeta m ON p.id=m.post_id
AND m.meta_key='_price'
What do you want to do with the prices? Update them?
To set the prices to a specific value, it is quite simple. Set price to whatever you want.
UPDATE wp_postmeta m
JOIN wp_posts p ON m.post_id=p.id
AND m.meta_key = '_price'
AND p.post_type = 'product'
SET m.meta_value = <price>
Problem
I have roughly 10,000+ products in WooCommerce version 2.1.12, WordPress version 3.9.1 and through an import error, the sale price field was populated with a "0" in every product, thereby making every product now free. So, what I need to do, of course, is a query to remove all of them, but I just can not seem to find the tables the prices are in. I did an extensive search, I think, trying to find this in both Google and here. The closest I came was this question: Need to get productdata out of mysql database which looks as though it's in the meta fields, but I can not visually see them there. Anyone know the specifics of where to find the prices, and maybe a tad bit of help on the query structure? Thanks for your time, NE