How to Mass Update MySQL Entries with Conditional Values Using CASE?

Mass Update MySQL Entries with Conditional Values Using CASE

In MySQL, you can update table entries based on specific conditions using the CASE statement. Here's how to do it:

Problem: Matching IDs for Price Update

You have the following query:

update ae44
set Price = Case
when ID = 'AT259793380' then '500'
when ID = 'AT271729590' then '600'
when ID = 'AT275981020' then '700'
end case

Your goal is to mass update listing prices based on corresponding IDs. However, the query is not returning the intended results.

Solution: Using CASE for Conditional Update

To fix the query, you need to use the CASE statement properly. Here's the correct syntax:

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

Note that this query will update prices for records with other IDs to NULL.

Alternative Solution: Excluding Null Updates

If you want to update only the IDs from your list and exclude null updates, you can use the following query:

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 will only update the prices for the IDs specified in the temporary table created using the UNION ALL statement.

The above is the detailed content of How to Mass Update MySQL Entries with Conditional Values Using CASE?. For more information, please follow other related articles on the PHP Chinese website!