phpmysqljoomla3.0

How to update multiple values in one table column with just one query?

My jwqt5_rsform_submission_values table has a column FieldValue with two values that I would like to respectively change.

I want to change value1 to firstvalue and value2 to secondvalue.

My PHP/Joomla code:

$db = JFactory::getDbo();
$db->setQuery("UPDATE `jwqt5_rsform_submission_values` SET `FieldValue` = 'firstvalue' WHERE `FieldValue` = 'value1'");
return $db->loadResult();

$db1 = JFactory::getDbo();
$db1->setQuery("UPDATE `jwqt5_rsform_submission_values` SET `FieldValue` = 'secondvalue' WHERE `FieldValue` = 'value2'");
return $db1->loadResult();

Can this update be executed in a single query?

Solution

  • You can combine the queries like this:

    UPDATE jwqt5_rsform_submission_values
SET FieldValue = CASE FieldValue
    WHEN 'value1' THEN 'firstvalue'
    WHEN 'value2' THEN 'secondvalue'
END
WHERE FieldValue IN ('value1', 'value2')