There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO

However it does not work quite the same as 'ON DUPLICATE UPDATE'

1. It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key

2. You can't reference the values in the old rows so you can't do an equivalent of

   INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) 
   ON DUPLICATE KEY UPDATE
   id=1, a=2, b=3, c=c + 1;
   

I'd like to use the work around to get the ID to!

That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.

However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.

Someone else suggested before you can reduce some typing by doing:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

(I have changing average to avg_score to make it easier for me to read.)

INSERT INTO stats (productid, avg_score ,votecount)
    VALUES (116689, 4, 1) 
    ON DUPLICATE KEY UPDATE
        avg_score = (avg_score * votecount + VALUES(avg_score)) / (votecount + 1),
        votecount = votecount + 1;

In newer versions of MySQL, the syntax will change: VALUES(avg_score) --> NEW.avg_score

So I decided to TEST both methods in my application and using the actual database. In the performed test, I'll refer to "METHOD 1" as the one in which we check for affected rows and insert if necessary, and subsequently refer to "METHOD 2" as using INSERT INTO.. ON DUPLICATE KEY UPDATE. Both methods have been ran on actual data 20 times with at least 5 seconds gap between tests:

So as to answer my own doubt, at least in case of UPDATES, using the 1st method is the winner, though the 2nd one isn't bad either.

use REPLACE INTO

The meaning of REPLACE INTO is that IF the new record presents new key values, then it will be inserted as anew record.

IF the new record has key values that match a pre-existing record,then the key violation will be ignored and the new record will replace the pre-existing record.

I was looking for the same behavior using jdbi's BindBeanList and found the syntax is exactly the same as Peter Lang's answer above. In case anybody is running into this question, here's my code:

  @SqlUpdate("INSERT INTO table_one (col_one, col_two) VALUES <beans> ON DUPLICATE KEY UPDATE col_one=VALUES(col_one), col_two=VALUES(col_two)")
void insertBeans(@BindBeanList(value = "beans", propertyNames = {"colOne", "colTwo"}) List<Beans> beans);

One key detail to note is that the propertyName you specify within @BindBeanList annotation is not same as the column name you pass into the VALUES() call on update.

This is our final solution, works like a charm!

The insert ignore will make sure that the row exists on both the master and slave, in case they've ever diverted.

The update ... where makes sure that only the most recent update, globally, is the end result after all replication is done.

mysql> desc test;
+-------+--------------+------+-----+-------------------+-------+
| Field | Type         | Null | Key | Default           | Extra |
+-------+--------------+------+-----+-------------------+-------+
| id    | int(11)      | NO   | PRI | NULL              |       | 
| value | varchar(255) | YES  |     | NULL              |       | 
| ts    | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+--------------+------+-----+-------------------+-------+

mysql> insert ignore into test values (4, "foo", now());    
mysql> update test set value = "foo", ts = now() where id = 4 and ts <= now();