which one is faster: "insert on duplicate update" OR "update with case" Table:

CREATE TABLE t5 (
 col1 VARCHAR(36) NOT NULL PRIMARY KEY, 
 col2 int(11) DEFAULT NULL, 
 col3 int(11) DEFAULT NULL, 
 UNIQUE (col2)
);


Records already inside:

insert into t5(col1, col2, col3) values 
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1), 
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);


Which of these two queries are expected to perform better:

Query-1:

insert into t5(col1, col2, col3) values 
 ('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888), 
 ('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999), 
on duplicate key update 
 col2=if(values(col2) IS NULL, col2, values(col2)), 
 col3=if(values(col3) IS NULL, col3, values(col3));


Query-2:

update t5 set 
 col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END), 
 col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END) 
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');


Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.

Condition of the table and the frequency the above query is performed as below:


number of records is in hundreds of thousand
query is performed once every x-seconds, each with ~150 keys


The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)
col2=if(values(col2) IS NULL, col2, values(col2)) is equal to col2=COALESCE(values(col2), col2). 
If ODKU causes UNIQUE (col2) violation, the whole query will fail (see fiddle, first query). 
The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle). 


fiddle



Finally:


  which faster: “insert on duplicate update” OR “update with case”


If no errors in queries and data INSERT .. ODKU is faster.