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

Question

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.

Answer 1

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),
     ...

Answer 2

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

Answer 3

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.

Answer 4

Let I have 2 tables in database: (1) brand (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);     
  })

  })}

Javascript code to insert into brand_item table : ON DUPLICATE KEY UPDATE is taken care of in INSERT query.

 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.

Answer 5

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