We had in our WordPress/Woocommerce table (hence mySQL) ab_postmeta the sale (discount) prices for some products.
We thought that reseting the sale prices via SQL query (and not via WordPress because they were too many) would be ok. What we did was:
Update ab_postmeta set meta_value='' where meta_key='sale_price'
For some parts of WordPress it worked for other ones it did not. At a closer inspection we saw that the ab_postmeta table had also other values which should be reset to the normal price in order to work. Our main difficulty is that the table has a vertical key=value layout with the following structure:
- meta_id - PK
- post_id - FK to the post/product
- meta_key - key
- meta_value - value of the key (property) above
Basically, we need an Update query which will take the (numeric) value of the meta_key='_max_variation_regular_price' and put it in meta_key='_price' and in meta_key='_max_variation_sale_price' and in other key values for the same post_id. It is ok if we run the SQL query multiple times for each target key from console.
How to do that? (I'm afraid to experiment, because I'm on a live db).
Here is a sample of data: (what we need to do is: all „263”s must become „329”s - We must copy from one source record in another destination record based on Post_id (must be the same) and meta_key - must have the corresponding values):
UPDATE 2: In fact we need a translation in SQL of the following: "For each
post_id take the value from
meta_key='_max_variation_regular_price' and put it in
meta_value where (for example)
meta_key='_price' (We will change your example for each destination row we need)