if-statement: MySQL: Update two columns - one depends on the other

mercredi 5 août 2015

Let's say I have this query:

UPDATE table_A SET column_1 = IF (column_2 = 1, column_1, column_1 + 1), 
                   column_2 = 1

This works as expected - if column_2 = 0 then column_1 increases by one and column_2 is set to 1. If column_2 = 1 nothing changes.

But if I apply this on two tables:

UPDATE table_A a
LEFT JOIN table_B b ON b.id = a.id
SET b.column = IF (a.column = 1, b.column, b.column + 1),
    a.column = 1

only a.column is updated. Why?

if-statement