Case expression not working as expected with 'on duplicate key update' clause

Question

Through the following query, I want to update the ConcurrentJobs column only when the value in MaxConcurrentJobs column is greater than or equal to the value of ConcurrentJobs being updated.

insert into userjobinfo (UserId,ConcurrentJobs) values (335,2300)
            on duplicate key update ConcurrentJobs = case when values(MaxConcurrentJobs) >= ConcurrentJobs
                                    then ConcurrentJobs else values(ConcurrentJobs) end;

But the above query doesn't work as expected. It always updates the value 2300 in the concurrent jobs column even if the condition fails. What could be the reason for this? Where does the query fail?

That's a case expression, not a case statement. – jarlh Dec 09 '16 at 08:28 — jarlh, Dec 09 '16 at 08:28

score 0 · Answer 1 · answered Dec 09 '16 at 09:06

..VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Since values(MaxConcurrentJobs) refers to a column you are NOT inserting it is NULL so your condition always defaults to values(ConcurrentJobs).Use a trigger for that logic.

score 0 · Answer 2 · answered Dec 09 '16 at 11:48

You don't need to refer columns already in the row using VALUES(); that is for the new values in supplied by the INSERT. I think the following should work:

insert into userjobinfo (UserId,ConcurrentJobs)
    values (335, 2300)
    on duplicate key update
        ConcurrentJobs = (case when MaxConcurrentJobs >= VALUES(ConcurrentJobs)
                               then VALUES(ConcurrentJobs)
                               else ConcurrentJobs
                          end);

Case expression not working as expected with 'on duplicate key update' clause

2 Answers2