5

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

Community
  • 1
  • 1
RockwareIT
  • 51
  • 1
  • 2

3 Answers3

4

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>
Pelmered
  • 2,727
  • 21
  • 22
  • Minor correction, the post type is `product` without any prefix. – brasofilo Jul 05 '14 at 10:21
  • Yes, that's right. Mixed it up with the class name I think :) – Pelmered Jul 06 '14 at 17:48
  • 1. Editing only `_price` is not enough. There is also `_regular_price` and `_sale_price`. Sale price is optional. Regular always exists. 2. In most cases, checking for the `post_type` is unnecessary. – tivnet Oct 15 '15 at 21:21
  • Isn't regular price only used when the product is on sale? There is a post type check in one of queries. I don't know exactly what was asked for here so I couldn't write a much more specific query. – Pelmered Oct 15 '15 at 21:48
  • I have to do something similar with products with a size variation. This is the query I used to get the fields I needed to change. – Hassan Voyeau Feb 15 '16 at 01:18
2

We released PW WooCommerce Bulk Edit to the WordPress.org repo a few days ago. This would help in your situation without needing to get into the database.

The plugin is free and would help clean up your prices. Full disclosure: there is a paid version that has more fields/features.

https://wordpress.org/plugins/pw-bulk-edit

Torre Lasley
  • 7,243
  • 3
  • 24
  • 12
  • I was always suspicious of programs that do something that you could do with a few lines of code, but I felt lazy and tried it, and it was a pleasant surprise. I wouldn't recommend it for 10000+ products though, it took a minute or two to update 100 – robotik Sep 02 '22 at 07:47
0

I needed to do something like this. I had to update prices for 110 products. All products had a variation on size with the price being the same for all sizes. This is the query I used to get all the fields I needed to change on the backend. I searched online and I did a search for all meta keys that had price in the name. The price on the frontend was not changing after the update SQL. What I discovered was that if I did a bulk edit (of regular price since this does not work for variation prices) on one product then all the prices reflected the change in the database. Strange. Maybe a caching thing.

SELECT p.id, IF( p.post_parent =0, p.id, p.post_parent ) AS parent, p.post_type, p.post_title, m.meta_key, m.meta_value
FROM wp_posts p
INNER JOIN wp_postmeta m ON p.id = m.post_id
WHERE
    (m.meta_key IN ('_price','_regular_price') AND p.post_type = 'product_variation') OR
    (m.meta_key IN ('_max_variation_price','_min_variation_price','_max_variation_regular_price','_min_variation_regular_price') AND p.post_type = 'product')
ORDER BY parent
Hassan Voyeau
  • 3,383
  • 4
  • 22
  • 24