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

I have a SQL query where I want to insert multiple rows in single query. so I used something like:

$sql = "INSERT INTO beautiful (name, age)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),
  ('Samia', 22),
  ('Hui Ling', 25),
  ('Yumie', 29)";

mysql_query( $sql, $conn );

The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. 'name' above, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.

For instance, in the example below, if 'Katrina' is already present in the database, the whole row, irrespective of the number of fields, should be updated. Again if 'Samia' is not present, the row should be inserted.

I thought of using:

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

Here is the trap. I got stuck and confused about how to proceed. I have multiple rows to insert/update at a time. Please give me a direction. Thanks.

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 Tg
using  @source as Sc
on tg.namet=sc.name 

when matched then update 
set tg.age=sc.age

when not matched then 
insert (name, age) VALUES
(SC.name, sc.age);

You can use Replace instead of INSERT ... ON DUPLICATE KEY UPDATE.

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.