MySQL replace all where xx=nothing

[antdk]

Hi,

So i got a thing i cant really solve.

The thing is ive moved a shop from one system to another. Now the issues is i need to replace some fields "if" they are empty or 0 - and further more, these fields should be replaces with a value of a current one of a associated id.

Example of mysql table wp_postmeta:

meta_id    post_id    meta_key             meta_value
1054        434          _price        
1055        434          _regular_price    1379.95
1056        434          _sale_price    
3070        613          _price                  2898.95    
3071        613          _sale_price    

What i want to do is to: IF _regular_price is NOT = nothing or 0 AND _sale_price = nothing OR 0, SET _sale_price to the same as _regular_price.

The important thing now, is that it takes the _regular_price from the matching post_id.

So it should copy the _regular_price of 1379.95 (from post_id 434) into "_sale_price" (of post_id 434). I got like 1000+ this is needed for.

How can this be done with a mysql command?

[Psycho]

I would ask why you are storing the data like that rather than separate columns for price, regular_price, sale_price, etc. but I will assume you are using some pre-built application and that is the only way for you to add additional details that were not supported by the app. But, if this is not the case, then you should definitely fix the table.

 

OK, I will also assume that this will be a one-time operation. Therefore, I am not going to try and figure out the most efficient solution, but instead focus on getting a working solution.

 

You don't state if _sale_price and _regular_price exist for all unique post_id's. Your example data above seems to indicate that they may not. So, if _regular_price  does not exists do you need to INSERT a record for it?

Edited March 25, 2014 by Psycho

[Barand]

UPDATE queries don't like data being selected from the same table to do the update so I'd use a 3-step approach

CREATE TEMPORARY TABLE wp_postmeta2 LIKE wp_postmeta;

INSERT INTO wp_postmeta2 
    SELECT * FROM wp_postmeta WHERE meta_key = '_regular_price';
 
UPDATE wp_postmeta a
    JOIN wp_postmeta2 b USING (post_id)
SET a.meta_value = b.meta_value
WHERE a.meta_key = '_sale_price' AND (a.meta_value IS NULL OR a.meta_value=0);

Giving:

+---------+---------+----------------+------------+
| meta_id | post_id | meta_key       | meta_value |
+---------+---------+----------------+------------+
|    1054 |     434 | _price         |       NULL |
|    1055 |     434 | _regular_price |    1379.95 |
|    1056 |     434 | _sale_price    |    1379.95 |
|    3070 |     613 | _price         |    2898.95 |
|    3071 |     613 | _sale_price    |       NULL |
+---------+---------+----------------+------------+

[kicken]

update wp_postmeta sprice

left join wp_postmeta rprice on rprice.post_id = sprice.post_id and rprice.meta_key = '_regular_price'

set

	sprice.meta_value = CASE 

		WHEN COALESCE(rprice.meta_value,'0') > 0 AND COALESCE(sprice.meta_value, '0') IN ('', '0') THEN rprice.meta_value

		ELSE sprice.meta_value

	END

WHERE

	sprice.meta_key = '_sale_price'

Something like that. You may need to play with the values in the WHEN condition depending on what is in your database. Or you could run a few other updates first to normalize the values (ie, make sure all necessary _sale_price/_regular_price fields are '0' rather than a mix of '', '0', or NULL) then just use a simple condition in the WHEN clause.

[Psycho]

I ran a test using the following query that worked. I will defer to Barand that this may not be the preferred approach, but it worked for me.

 

UPDATE wp_postmeta AS wp1
JOIN wp_postmeta AS wp2
  ON wp1.post_id = wp2.post_id
 AND wp1.meta_key = '_sale_price'
 AND wp2.meta_key = '_regular_price'
SET wp1.meta_value = IF(NOT wp1.meta_value, wp2.meta_value, wp1.meta_value)

 

However, this would not resolve the problem of a scenario where a _sale_price record does not currently exist for a specific post_id where a _regular_price record does exist. You could run an INSERT query first to add those missing records if you need them.