Problem
I’m working on a SQL query that will insert several rows in a single query. As a result, 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 issue is that when I run this query, I want to see if a UNIQUE key (not the PRIMARY KEY), such as ‘name’ above, exists. If it does, the accompanying full row should be updated; otherwise, it should be inserted.
If the word ‘Katrina’ already exists in the database, for example, the entire row, regardless of the amount of fields, should be updated. If ‘Samia’ is missing, the row should be added again.
I considered using:
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29) ON DUPLICATE KEY UPDATE
Here’s the snare. I became stopped and unsure of how to proceed. I’m inserting/updating numerous rows at once. Please point me in the right path. Thanks.
Asked by Prashant
Solution #1
You can use an alias for the row starting with MySQL 8.0.19. (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
...
Use the term VALUES in earlier versions (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),
...
Answered by Peter Lang
Solution #2
INSERT INTO … ON DUPLICATE KEY UPDATE will only work for MYSQL, not for SQL Server.
To work around issue in SQL Server, create a temp table, insert values into 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);
Answered by li rachel
Solution #3
I was seeking for the same behavior with jdbi’s BindBeanList and discovered that the syntax is identical to Peter Lang’s solution. Here’s my code in case anyone else has this question:
@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 important distinction to keep in mind is that the propertyName specified in the @BindBeanList annotation is not the same as the column name passed into the VALUES() method on update.
Answered by Bill Weisberger
Solution #4
Let’s say I have two database tables: (1) brand and (2) brand item.
DROP TABLE IF EXISTS `brand`;
CREATE TABLE `brand` (
`brand_id` int(11) NOT NULL AUTO_INCREMENT,
`brand_key` varchar(255) DEFAULT NULL,
`brand_name` varchar(2048) DEFAULT NULL,
`disclaimer` varchar(2048) DEFAULT NULL,
`description` varchar(2048) DEFAULT NULL,
`short_description` varchar(2048) DEFAULT NULL,
`terms` varchar(2048) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`last_updated_date` datetime DEFAULT NULL,
`always_show_disclaimer` varchar(2048) DEFAULT NULL,
`disclaimer_instructions` varchar(2048) DEFAULT NULL,
`display_instructions` varchar(2048) DEFAULT NULL,
`terms_and_conditions_instructions` varchar(2048) DEFAULT NULL,
`image_urls` json DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`feature` boolean DEFAULT '1',
PRIMARY KEY (`brand_id`),
UNIQUE KEY `brand_key` (`brand_key`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `brand_item`;
CREATE TABLE `brand_item` (
`brand_id` int(11) DEFAULT NULL,
`utid` varchar(255) DEFAULT NULL,
`reward_name` varchar(2048) DEFAULT NULL,
`currency_code` varchar(2048) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`value_type` varchar(255) DEFAULT NULL,
`reward_type` varchar(255) DEFAULT NULL,
`is_whole_amount_value_required` varchar(255) DEFAULT NULL,
`face_value` double(16,2) DEFAULT '0.00',
`min_value` double(16,2) DEFAULT '0.00',
`max_value` double(16,2) DEFAULT '0.00',
`created_date` datetime DEFAULT NULL,
`last_updated_date` datetime DEFAULT NULL,
`redemption_instructions` varchar(2048) DEFAULT NULL,
`countries` varchar(2048) DEFAULT NULL,
UNIQUE KEY `utid` (`utid`),
KEY `brand_id` (`brand_id`),
CONSTRAINT `brand_item_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Javascript code to insert into brand table : ON DUPLICATE KEY UPDATE is taken care of in INSERT query.
async function addToDB(event, brandDetails) {
return new Promise((resolve, reject) => {
let values = `[brandDetails.key,brandDetails.name,brandDetails.disclaimer,brandDetails.description,brandDetails.shortDescription,brandDetails.terms,brandDetails.createdDate,brandDetails.lastUpdateDate,brandDetails.alwaysShowDisclaimer,brandDetails.disclaimerInstructions,brandDetails.displayInstructions,brandDetails.termsAndConditionsInstructions,brandDetails.imageUrls,brandDetails.status]`
let query = "INSERT INTO ?? (brand_key,brand_name,disclaimer,description,short_description,terms,created_date,last_updated_date,always_show_disclaimer, disclaimer_instructions,display_instructions,terms_and_conditions_instructions,image_urls,status) VALUES (?) ON DUPLICATE KEY UPDATE brand_key=?, brand_name=?, disclaimer=?, description=?, short_description=?, terms=?, created_date=?,last_updated_date=?, always_show_disclaimer=?, disclaimer_instructions=?,\ display_instructions=?,terms_and_conditions_instructions=?, image_urls=?, status=?";
MySQLController.executeQuery(event,query, [BRAND_TABLE, values, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7],values[8],values[9],values[10],values[11],values[12],values[13],values[14]] )
.then((res)=>{
console.log("Success in Insertion ", res);
resolve(res);
})
.catch((err)=>{
console.log("error in DB ",err);
reject(err);
})
})}
Insert the following Javascript code into the brand item table: The INSERT query handles ON DUPLICATE KEY UPDATE.
async function addToBrandItem(event, fkey, brandItemDetails) {
return new Promise((resolve, reject) => {
let values = [fkey, brandItemDetails.utid, brandItemDetails.rewardName, brandItemDetails.currencyCode, brandItemDetails.status, brandItemDetails.valueType, brandItemDetails.rewardType,brandItemDetails.isWholeAmountValueRequired, `${brandItemDetails.faceValue}`, `${brandItemDetails.minValue}`, `${brandItemDetails.maxValue}`, brandItemDetails.createdDate,brandItemDetails.lastUpdateDate,brandItemDetails.redemptionInstructions,`${brandItemDetails.countries}`]
let query = "INSERT INTO ?? (brand_id,utid,reward_name,currency_code,status,value_type,reward_type,is_whole_amount_value_required,face_value,min_value,max_value,created_date,last_updated_date,redemption_instructions,countries) VALUES (?)";
AuroraController.executeQuery(event,query , [BRAND_ITEM_TABLE, values, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7],values[8],values[9],values[10],values[11],values[12],values[13],values[14]] )
.then((res)=>{
console.log("Success in Insertion in Bran_item", res);
resolve(res);
})
.catch((err)=>{
console.log("error in DB ",err);
reject(err);
})
})}
Note:- To preserve decimal value in values array, I have tick symbol to get its value using ${}, else it will consider as a string in array.
Answered by Ajay
Solution #5
Instead of INSERT… ON DUPLICATE KEY UPDATE, you can use Replace.
Answered by a1ex07
Post is based on https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query