CODEWITHSUNDEEP
mysql
Rostislav Aleev
Rostislav Aleev

Reputation: 371

MySQL 8 update, set, case

I'm testing a multiple update SET col1 = col2 WHEN col1 != col2

I don't understand, why it doesn't work. I see in output only info about Row matched and nothing about affected rows

UPDATE db.t1
    JOIN db.t2
    ON  t1.id = t2.id
    JOIN db.t3
    ON t2.id = t3.id
SET col1 = CASE
    WHEN col1 != col2
    THEN col1 = col2
    END
WHERE t1.id = t2.id AND t3.id = t2.id and t1.id = 2

Upvotes: 0

Views: 41

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

Be caferul to this .. THEN col1 = col2 .. in this case teh code is evaluated as logical condition (and the resul could be 0 or 1) ..

if you want set col2 to col1 depending on case result then you should use 

UPDATE db.t1
JOIN db.t2 ON  t1.id = t2.id
JOIN db.t3  ON t2.id = t3.id
SET col1 = CASE
    WHEN col1 != col2
    THEN  col2
    END
WHERE t1.id = t2.id 
AND t3.id = t2.id 
AND t1.id = 2

Upvotes: 1

Related Questions