MySQL | Insert multiple records and update if record(s) already exist in a single query

|
| By Webner

In MySQL, we generally perform insert, update, select and delete operations on records of table. Multiple Insert operation can also be easily performed on table.

Take an example of Employees table with fields:

Multiple records can be inserted in Employees table in a single query using following MySQL query :


INSERT INTO employees (name, address, email, password, contact_number, department_id) values (‘abc’, ‘abc city’, ‘abc@mail.com’, ‘abc123456’, ‘765544 765’, 34),  (‘abc1’,  ‘abc1 city’, ‘abc1@mail.com’, ‘abc123456’, ‘765544 7651’, 34), (‘abc2’, ‘abc2 city’, ‘abc2@mail.com’, ‘abc123456’, ‘7265544 765’, 34);

Now, a situation arose in our project that required us to insert multiple records at the same time and if some of the records already exist, they should be updated based on email id.

On executing the above query, duplicate emails existed and gave database error “duplicate values exist” no new records were inserted.

To avoid database error and insert records successfully, add IGNORE clause like below :

INSERT IGNORE INTO employees (name, address, email, password, contact_number, department_id) values (‘abc’, ‘abc city’, ‘abc@mail.com’, ‘abc123456’, ‘765544 765’, 34),  (‘abc1’, ‘abc1 city’, ‘abc1@mail.com’, ‘abc123456’, ‘765544 7651’, 34), (‘abc2’, ‘abc2 city’, ‘abc2@mail.com’, ‘abc123456’, ‘7265544 765’, 34);

When we write IGNORE clause, query doesn’t cause duplicate error message.

If requirement is to update records having already existing emails, and insert new records in a single query, then we can use following MySQL query :

INSERT IGNORE INTO employees (name, address, email, password, contact_number, department_id) values (‘abc’, ‘abc city’, ‘abc@mail.com’, ‘abc123456’, ‘765544 765’, 34),  (‘abc1’, ‘abc1 city’, ‘abc1@mail.com’, ‘abc123456’, ‘765544 7651’, 34), (‘abc2’, ‘abc2 city’, ‘abc2@mail.com’, ‘abc123456’, ‘7265544 765’, 34) ON DUPLICATE KEY UPDATE name = VALUES(name), address = VALUES(address), password = VALUES(password), contact_number = VALUES(contact_number), department_id = VALUES(department_id);

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Web development or any other software development assistance please contact us at webdevelopment@webners.com

Leave a Reply

Your email address will not be published. Required fields are marked *