How to Update a MySQL Entry Using CASE for Dynamic Value Assignment?

How to Update an Entry in MySQL Effectively Using CASE for New Value

To update an entry in MySQL using the CASE statement for a new value, follow these steps:

• Syntax:

UPDATE table_name
SET column_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE valueN
END

• Example:

UPDATE ae44
SET price = CASE
    WHEN ID = 'AT259793380' THEN '500'
    WHEN ID = 'AT271729590' THEN '600'
    WHEN ID = 'AT275981020' THEN '700'
    ELSE NULL    -- Optional: Set values for the remaining matching ID values
END

Note: The query provided by the question updates prices for records with other IDs to NULL. To avoid this:

Option 1: Specify a Default Value:

UPDATE ae44
SET price = CASE
    WHEN ID IN ('AT259793380', 'AT271729590', 'AT275981020') THEN
        CASE
            WHEN ID = 'AT259793380' THEN '500'
            WHEN ID = 'AT271729590' THEN '600'
            WHEN ID = 'AT275981020' THEN '700'
        END
    ELSE
        price
    END

Option 2: Use a JOIN:

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

The above is the detailed content of How to Update a MySQL Entry Using CASE for Dynamic Value Assignment?. For more information, please follow other related articles on the PHP Chinese website!