How to insert or update multiple items in MySQL and WordPress?
You have a table with some data, lets say it is user information with emails, names, ages, etc. Now you need to insert 10 more rows of data but you don’t know if the 10 new rows are for new users or if they are attributes for users you already have in the database. This is a very common scenario where you need to figure out if you should update or insert.
It would not be optimal to write a loop and for each row, check if email exists (assuming this is the unique criteria for a user), and then insert or update. You want to offload such logic to the database and do it all with 1 query. This leaves you with a choice of using REPLACE INTO or INSERT on DUPLICATE KEY UPDATE. REPLACE INTO seems like a great solution considering that the query has the same syntax as a normal INSERT INTO. But, it should probably be avoided as it actually performs a DELETE and INSERT operation and updates all indexes.
So, INSERT on DUPLICATE KEY UPDATE is most likely the optimal query but how do you do that for multiple rows? And, how do you do that based on multiple criteria for uniqueness? Maybe I want to allow the same emails in the table as they belong to a different list or a different user which are different columns in the table. So, email by itself is not the only unique criteria for a user but the unique criteria is based on multiple columns.
Let’s start with a simple mysql users table and insert some initial data with a multiple insert into statement.
CREATE TABLE users (
id int(20) NOT NULL AUTO_INCREMENT,
listid INT,
email VARCHAR(50),
age INT,
name VARCHAR(150),
);
insert into users (listid,email,age,name) values (1,'john@gmail.com',22,"John"),
(1,'kate@gmail.com',24,"Kate"),
(1,'mary@gmail.com',22,"Mary"),
(2,'john@gmail.com',22,"John");
Notice that John, the same user, is inserted twice but we want to allow that as John belongs to a different list or listid. Now, when using INSERT on DUPLICATE KEY UPDATE, we need to specify the criteria that the database needs to check in order to decide if it should update or insert. We need a unique key, and MySQL allows us to specify multiple columns via a composite key, which uniquely indentifies an entity occurrence. So, we either alter the table above and add a composite primary key or define it from the start:
//drop the old primary key if it exists: ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (email, listid);
//or create table with composite primary key from the start
CREATE TABLE users (
id int(20) NOT NULL AUTO_INCREMENT,
listid INT,
email VARCHAR(50),
age INT,
name VARCHAR(150),
PRIMARY KEY (email, listid)
);
Now our database table is ready to batch insert ON DUPLICATE KEY UPDATE. Here is an example:
//syntax to insert on duplicate update with one row
//INSERT INTO users (val1, val2, val3, val4) VALUES (1, 2, 3, 4)
//ON DUPLICATE KEY UPDATE val1=1, val2=2, val3=3, val4=4;
INSERT INTO users (listid,email,age,name) VALUES (3,'john@gmail.com',26,"John"),
(3,'bob@gmail.com',32,"Bob"),
(3,'matt@gmail.com',29,"Matt"),
(2,'john@gmail.com',25,"John"),
ON DUPLICATE KEY UPDATE listid=VALUES(listid),
email=VALUES(email),
age=VALUES(age),
name=VALUES(name);
When this runs, the first 3 rows are new and get inserted but the last row gets updated. I needed to execute such a query on WordPress so I decided on changing this wp_insert_rows method for inserting multiple rows in WP into a method that does ON DUPLICATE KEY UPDATE:
WordPress Multiple Insert function with on Duplicate Key Update
External:
