In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)
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 &lt;&gt; '_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 &lt;&gt; '_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 &lt;&gt; '_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 &lt;&gt; '_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.