[SOLVED] MySQL how to Update multiple rows with conditions in the same table

MySQL how to Update multiple rows with conditions in the same table

I have a question, I want to update values in a table with conditions. I have a list of users that I need to update their accounting_id, the key is user_id. I want to do it in a single query. The problem is that the query puts 0 in the accounting id. The DB is MySQL.

UPDATE users
SET users.accounting_id = CASE
        WHEN users.user_id = '102215' THEN users.accounting_id = '102'
        WHEN users.user_id = '102144' THEN users.accounting_id = '193'
    END    
WHERE users.user_id IN ('102215','102144');

Solution

Let's examine the expressions in the then part of your statement. e.g.: users.accounting_id = '102'. This is not an assignment, but a boolean expression, which evaluates to false, since accounting_id is not 102 (it's 102215 in this case). The = before the case is used for the assignment. In the then branches you just need to return the value to be assigned:

UPDATE users
SET users.accounting_id = CASE
        WHEN users.user_id = '102215' THEN '102'
        WHEN users.user_id = '102144' THEN '193'
    END    
WHERE users.user_id IN ('102215','102144');