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.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
The normal SQL INSERT query inputs the data values in a single row. In case when we want to insert data in multiple rows at once, this query fails. Thus, in order to save the execution time, we need to use the SQL INSERT query in such a manner that it injects data into multiple rows at once.
So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
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 new
ON 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 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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With