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

mysql on duplicate key update multiple values
mysql on duplicate key update multiple keys
on duplicate key update not working
mysql insert on duplicate key ignore
mysql on duplicate key update auto_increment
on duplicate key update python
on duplicate key update oracle
insert duplicate rows in sql

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.

Use keyword VALUES to refer to new values (see documentation).

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

Update multiple records by using ON DUPLICATE KEY UPDATE in , We will get a message saying 2 rows inserted, but actually we have updated one record only. Here mysql will retrun the number of affected rows based on the  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:

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);

INSERT on duplicate key UPDATE, The ON DUPLICATE KEY UPDATE clause can contain multiple column to use an alias for the row, with, optionally, one or more of its columns to be inserted, Using the row alias new , the statement shown previously using VALUES() to  MySQL insert row on duplicate key update multiple columns. I have a table (name, date, stat1, stat2, stat3), (name, date) is the PK. When I insert rows, there will be duplicate keys, and I need to sum up the three stats. I use the following query with PreparedStatement in Java:

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

INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in , MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query. INSERT INTO beautiful (name, age) VALUES. ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29) ON DUPLICATE KEY UPDATE. As MySQL doesn’t have inherent support for updating more than one rows or records with a single update query as it does for insert query, in a situation which needs us to perform updating to tens of thousands or even millions of records, one update query for each row seems to be too much.

MySQL INSERT ON DUPLICATE KEY UPDATE, Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE . ON DUPLICATE KEY UPDATE for multiple items. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query. 857. MySQL On Duplicate Key Update

How to insert or update multiple items in MySQL and WordPress , When you insert a new row into a table if the row causes a duplicate in UNIQUE index or The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows: INSERT After that, insert one more row into the devices table. INSERT · Insert Multiple Rows · INSERT INTO SELECT · Insert On Duplicate Key Update  With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

INSERT ON DUPLICATE KEY UPDATE, REPLACE INTO or INSERT on DUPLICATE KEY UPDATE. UPDATE is most likely the optimal query but how do you do that for multiple rows? //syntax to insert on duplicate update with one row //INSERT INTO users (val1,  With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

Comments
  • What if we need to include an IF statement in the UPDATE that has a different condition for each row?
  • @logic: There are several questions related to this: StackOverflow search. If none of them answers your question, please post a new one explaining exactly what you need.
  • Who uses SQL server though :D
  • Yes but using REPLACE the old row is deleted before the new row is inserted. I want to retain the old row for maintaining primary ids.
  • I didn't realize that retaining the old PK is somehow important to you... Surely, REPLACE won't work in this case...
  • PS: "replace into" requires both delete and insert privileges!
  • and for the above mentioned delete/insert reasons, its also much slower in these instances.
  • What if you want to update few columns? Replace will update all.