1

I have the following three update statements:

UPDATE mx m JOIN main_territorypricingavail a USING (tp_id) 
SET sd_price = a.price WHERE a.code = 'SDBUY';

UPDATE mx m JOIN main_territorypricingavail a USING (tp_id) 
SET hd_price = a.price WHERE a.code = 'HDBUY';

UPDATE mx m JOIN main_territorypricingavail a USING (tp_id) 
SET uhd_price = a.price WHERE a.code = 'UHDBUY';

Is it possible to combine these three into one statement?

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
David542
  • 104,438
  • 178
  • 489
  • 842

1 Answers1

4

You could use CASE expressions here to do this in a single update:

UPDATE mx m
INNER JOIN main_territorypricingavail a
USING (tp_id)
SET
    sd_price  = CASE WHEN a.code = 'SDBUY'  THEN a.price ELSE sd_price END,
    hd_price  = CASE WHEN a.code = 'HDBUY'  THEN a.price ELSE hd_price END,
    uhd_price = CASE WHEN a.code = 'UHDBUY' THEN a.price ELSE uhd_price END;

From a performance point of view, MySQL will not overwrite a value to a column/record in an update if it notices that the value would not change. So the penalty for the ELSE conditions happening often above may not be as bad as it looks (see here for more information).

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360