MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).

INSERT INTO beautiful (name, age)    VALUES    ('Helen', 24),    ('Katrina', 21),    ('Samia', 22),    ('Hui Ling', 25),    ('Yumie', 29)    AS newON DUPLICATE KEY UPDATE    age = new.age    ...

For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20).

INSERT INTO beautiful (name, age)    VALUES    ('Helen', 24),    ('Katrina', 21),    ('Samia', 22),    ('Hui Ling', 25),    ('Yumie', 29)ON DUPLICATE KEY UPDATE    age = VALUES(age),     ...

INSERT INTO ... ON DUPLICATE KEY UPDATE will only work for MYSQL, not for SQL Server.

for SQL server, the way to work around this is to first declare a temp table, insert value to that temp table, and then use MERGE

Like this:

declare @Source table(name varchar(30),age decimal(23,0))insert into @Source VALUES('Helen', 24),('Katrina', 21),('Samia', 22),('Hui Ling', 25),('Yumie', 29);MERGE beautiful  AS Tgusing  @source as Scon tg.namet=sc.name when matched then update set tg.age=sc.agewhen not matched then insert (name, age) VALUES(SC.name, sc.age);

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.