If statement on duplicate key

Question

I'm trying to insert/update the force_check value to 1 only if the department column is different from the old one. I'm using MySQL 8

So far I've tried this query, but it returns 0 rows affected

insert into user (id, department) values ('ABC', 'Dep1') 
ON DUPLICATE KEY UPDATE force_check = IF(department <> VALUES(department), 1, force_check);

The current row is

| id    | department | force_check |
|-------|------------|-------------|
| "ABC" | null       | 0           |

and it should be updated to:

| id    | department | force_check |
|-------|------------|-------------|
| "ABC" | null       | 1           |

Answer 1

Probably the problem is with null values: operator <> yields an undefined value when one of its operands is null, which might defeat your IF function.

We instead can use <=>, the null-safe equality operator:

insert into user (id, department) values ('ABC', 'Dep1') 
on duplicate key update 
force_check = case when department <=> values(department) then force_check else 1 end

Notes:

• I used case because it is standard SQL (IF would work too).
• Of course for this to work you need a unique key on id, so that the duplicates can be identified in the first place