Mysql – In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

MySQLupdateWordpress

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:

ab_postmeta

  • 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).

UPDATE:

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):

Sample data

UPDATE 2: In fact we need a translation in SQL of the following: "For each post_id take the value from meta_value where 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)

Best Answer

My translation of your problem is (slightly altered from your description):

For each post_id, find the value from meta_value column where meta_key = '_max_variation_regular_price' and put it in meta_value in all other rows with same post_id and where the meta key is a "price" (i.e. meta_key LIKE '%\_price')*.

Now, before we do anything and change the data in a production database, the best approach is to take a backup, restore the backup as another database and play around in the restored (copy) database, where we won't mind making mistakes.

Lets identify the rows that have the values we need:

-- query1
SELECT post_id, meta_value AS price
FROM ab_postmeta  
WHERE meta_key = '_max_variation_regular_price' ;

If (post_id, meta_key) has a UNIQUE constraint, that's all the values we need. If not, we may have to take an additional step to make sure that the result has one value for every post_id and not more (skipping this step).

Now, lets find the rows that need updating:

-- query2
SELECT post_id, meta_key, meta_value 
FROM ab_postmeta  
WHERE meta_key LIKE '%\_price' 
  AND meta_key <> '_max_variation_regular_price' ;

Then we can combine the two selects into one:

-- query3
SELECT upd.post_id, upd.meta_key, 
       upd.meta_value  AS old_price,
       good.meta_value AS good_price 
FROM ab_postmeta AS good             -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
WHERE good.meta_key = '_max_variation_regular_price' 
  AND upd.meta_key LIKE '%\_price' 
  AND upd.meta_key <> '_max_variation_regular_price' ;

We can run this and inspect the results to see what an update will. No UPDATE run yet!. After that - and if the inspection seems right - we can run the actual UPDATE. Note that it's very similar to the previous SELECT. The WHERE clauses are identical and the FROM clause has become UPDATE. But we'll do it inside a transaction, just to be sure:

BEGIN ;           -- start a transaction. 

UPDATE ab_postmeta AS good           -- the table with the good prices
  JOIN ab_postmeta AS upd            -- the table to be updated
    ON good.post_id = upd.post_id
SET
    upd.meta_value = good.meta_value
WHERE good.meta_key = '_max_variation_regular_price' 
  AND upd.meta_key LIKE '%\_price' 
  AND upd.meta_key <> '_max_variation_regular_price' ;

Lets run query2 again, to check the new values:

SELECT post_id, meta_key, meta_value 
FROM ab_postmeta  
WHERE meta_key LIKE '%\_price' 
  AND meta_key <> '_max_variation_regular_price' ;

Now, is everything OK? Do all the updates look good?

  • If no, run ROLLBACK ; and no harm was done. Start again, by evaluating your algorithm or running with a smaller set.

  • If yes, run COMMIT ; and the changes will be permanent. You can safely do this (again) in production.

* Thnx to Andriy for the bugfix. the LIKE should escape the _ character with \_ as it's a wildcard.

Related Question