Home >Database >Mysql Tutorial >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:
UPDATE table_name SET column_name = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ... ELSE valueN END
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!