How to Correctly Update Rows in MySQL Using CASE for Value Assignment?

Updating Rows in MySQL Using CASE for Value Assignment

In order to update specific rows in a MySQL database using the CASE WHEN statement, the correct syntax must be followed. The provided query, which attempts to update prices based on ID values, demonstrates an incorrect usage of the CASE statement.

To resolve this, the correct update query should be as follows:

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

In this corrected query, note that:

• The CASE WHEN statement is properly structured with individual WHEN clauses.
• The assignment of the new value is done using := syntax.
• The query ends with a semicolon (;).

Updating rows using CASE statement can be useful when assigning different values based on specific conditions. However, it's important to note that the provided query will update prices for all records, regardless of their ID. If the goal is to update only the specific IDs mentioned in the query, the following approach can be used:

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 Correctly Update Rows in MySQL Using CASE for Value Assignment?. For more information, please follow other related articles on the PHP Chinese website!