Mysql – Combining multiple queries into a single query

mysql-5.6performancequery-performance

I've got checkbox group field with 4 days of the week – Thursday, Friday, Saturday & Sunday. I populate this field based on whether 4 separate text fields for each corresponding day of the week are populated or not.

At the moment, I'm doing this by running 15 separate queries! This is every combination of Thursday, Friday, Saturday & Sunday I can think of, which then updates the value for this checkbox group field.

The queries are listed below for info. My question is, can anyone think of a way to update this checkbox group field with a single query rather than 15 separate queries? Thanks for any help. Regards, Stephen.

UPDATE exp_channel_data SET field_id_285 = "Thursday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Saturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Sunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Saturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");

Best Answer

Yes, you can update the group in one go using an UPDATE statement like this:

UPDATE
  exp_channel_data
SET
  field_id_285 =
    CONCAT_WS(
      '\n',
      CASE WHEN field_id_151 <> '' THEN 'Thursday' END,
      CASE WHEN field_id_152 <> '' THEN 'Friday'   END,
      CASE WHEN field_id_153 <> '' THEN 'Saturday' END,
      CASE WHEN field_id_154 <> '' THEN 'Sunday'   END
    )
WHERE
  channel_id = 14
  AND (field_id_151 <> ''
    OR field_id_152 <> ''
    OR field_id_153 <> ''
    OR field_id_154 <> ''
  )
;

Each of the CASE expressions produces either a week day name or null. The CONCAT_WS function concatenates the names using \n as the delimiter, omitting the nulls, if any.

However, even though you can do this with a single statement, the fact remains that this is a terrible design. Without more information it is hard to suggest a proper alternative but it seems likely that a junction table could be used here instead.

Related Question