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

Mass Updating MySQL Entries with CASE for New Values

In MySQL, updating an entry using the CASE expression allows you to assign a different value to a column based on a specific condition. To fix the provided query, remove the comma after the set keyword and format the CASE expression as follows:

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

This query will update the corresponding prices for the specified IDs. However, it's important to note that it will also set the price value to NULL for any records with IDs that are not included in the CASE expression.

If you want to only update the prices for the specific IDs in your list, use a query like this:

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 uses a subquery to create a table with the old and new values for the IDs, then it joins this table with the ae44 table and performs the update only on the matching records.

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