SQL Server Merge vs MySQL Insert On Duplicate Key performance comparison
IODKU is the best of the options in MySQL.  It works something like this:


Use some UNIQUE key (possibly the clustered, unique, PRIMARY KEY) to locate the row to modify.
If no such row, perform an INSERT.
If such a row, perform an UPDATE.


You can't get faster than that.

Note that step 1 will cache everything that is needed for #2 or #3 in the buffer_pool.  (Well, OK, secondary indexes are handled in a 'delayed' way via the "Change Buffer".)

Further note that the statement is atomic, whereas your 2-statement alternatives need to be in a transaction.

Keep in mind that REPLACE is DELETE (0, 1, or possibly multiple rows, if you have multiple unique keys), then INSERT.  Note that the AUTO_INCREMENT (if used) value is thrown away and a new one is created.

As for that messy query, it can probably be done with GREATER() and/or IF() and/or VALUES().  Assuming that you are trying to merge multiple rows in, you need the IODKU+SELECT syntax:

INSERT INTO Target (key, a)
    ON DUPLICATE KEY UPDATE
        SET a = VALUES(a) 
    SELECT Target.key, Target.a
        FROM Target
        JOIN Source ON Source.key = Target.key
        WHERE Target.x &gt; 'value';


Since I don't know what MERGE does, I can't give you all the details.