How can I efficiently update MySQL entries with new values using the CASE statement?

Updating MySQL Entries Using CASE for New Values

This article addresses the challenge of mass-updating listing prices based on ID in a MySQL database. A common issue encountered when attempting this is using the CASE statement, which, when improperly used, may lead to unexpected results.

The query provided in the question contains a fundamental flaw. It employs the CASE statement to set the Price column, but it doesn't explicitly specify the value for records with IDs not included in the WHEN clauses. Consequently, those records will be updated with NULL values.

To resolve this, the correct query structure should resemble the following:

UPDATE  ae44
SET     price =
        CASE
        WHEN ID = 'AT259793380' THEN
                '500'
        WHEN ID = 'AT271729590' THEN
                '600'
        WHEN ID = 'AT275981020' THEN
                '700'
        END

However, this query still has a drawback: it will update records with IDs not listed in the WHEN clauses with NULL values. To restrict the update to only the specified IDs, a more selective approach is needed:

UPDATE  ae44
JOIN    (
        SELECT  'AT259793380' AS oldval, '500' AS newval
        UNION ALL
        SELECT  'AT271729590' AS oldval, '600' AS newval
        UNION ALL
        SELECT  'AT275981020' AS oldval, '700' AS newval
        ) q
ON      ae44.id = q.oldval
SET     price = q.newval

This query utilizes a subquery to create a temporary table with the specified old and new values. It then uses a JOIN to match the records in the ae44 table with those in the subquery based on ID. Only records with matching IDs will have their prices updated to the corresponding new value, leaving all other records unchanged.

The above is the detailed content of How can I efficiently update MySQL entries with new values using the CASE statement?. For more information, please follow other related articles on the PHP Chinese website!