Questions › MySQL - on duplicate key - CASE WHEN THEN ELSE doesnt work

I want to insert an entry (uid, A, B) to database. If the entry is existed already, I will update the 'A' and 'B' column with condition: (1 = old entry; [2] = new entry)

A = (B[1] == B[2]) ? A[1] : A[2];
B = B[2] + 1;

My query:

INSERT INTO TableName (uid,A,B) 
VALUES (uid,A[2],B[2]) 
ON DUPLICATE KEY 
UPDATE  A= (SELECT CASE B WHEN B[2] THEN A ELSE A[2] END), 
        B= B[2]+1;

Ex1:

Old entry : uid = 1; A = 2; B = 4;
New entry : uid = 1; A = 3; B = 4;
-> Should be: A = 2; B = 5;
But my result: A = 3; B = 5; -> FAIL

Ex2:

Old entry : uid = 1; A = 2; B = 4;
New entry : uid = 1; A = 3; B = 6;
-> A = 3; B = 7; (my code works well in this ex) -> OK

Thank you very much.

Answer 1: remove SELECT key make it work

INSERT INTO TableName (uid,A,B) 
VALUES (uid,A[2],B[2]) 
ON DUPLICATE KEY 
UPDATE  A= (CASE B WHEN B[2] THEN A ELSE A[2] END), 
        B= B[2]+1;

Answer of p-jairaj

INSERT INTO TableName (uid,A,B) 
VALUES (uid,A[2],B[2]) 
ON DUPLICATE KEY 
UPDATE  A= (IF(B=B[2],A,A[2])), 
        B= B[2]+1;
Comments :
AdamMc331 replied

What doesn't work? Do you get a syntax error?


1 Answers :
Anh-Tuan Mai answered

Try this:

INSERT INTO TableName (uid,A,B) 
VALUES (uid,A[2],B[2]) 
ON DUPLICATE KEY 
UPDATE  A= (IF(B=B[2],A,A[2])), 
        B= B[2]+1;