CODEWITHSUNDEEP
mysqlsqlbulk
rid
rid

Reputation: 63442

MySQL bulk INSERT or UPDATE

Is there any way of performing in bulk a query like INSERT OR UPDATE on the MySQL server?

INSERT IGNORE ...

won't work, because if the field already exists, it will simply ignore it and not insert anything.

REPLACE ...

won't work, because if the field already exists, it will first DELETE it and then INSERT it again, rather than updating it.

INSERT ... ON DUPLICATE KEY UPDATE

will work, but it can't be used in bulk.

So I'd like to know if there's any command like INSERT ... ON DUPLICATE KEY UPDATE that can be issued in bulk (more than one row at the same time).

Upvotes: 36

Views: 72150

Answers (5)

Christopher McGowan
Christopher McGowan

Reputation: 1391

Although this question has been answered correctly already (that MySQL does support this via ON DUPLICATE UPDATE with the expected multiple value set syntax), I'd like to expand on this by providing a demonstration that anyone with MySQL can run:

CREATE SCHEMA IF NOT EXISTS `test`;
DROP TABLE IF EXISTS test.new_table;
CREATE TABLE test.new_table (`Key` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`Key`)) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8;

SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;

The output is as follows:

Empty set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-----+
| Key |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
5 rows in set (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 5  Duplicates: 5  Warnings: 0

+-----+
| Key |
+-----+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+-----+
5 rows in set (0.00 sec)

Upvotes: 4

user359996
user359996

Reputation: 5693

You can insert/update multiple rows using INSERT ... ON DUPLICATE KEY UPDATE. The documentation has the following example:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Or am I misunderstanding your question?

Upvotes: 84

Kibbee
Kibbee

Reputation: 66112

One possible way to do this is to create a temporary table, insert the data into that, and then do 1 query with a join to insert the records that don't exist followed by and update to the fields that do exist. The basics would be something like this.

CREATE TABLE MyTable_Temp LIKE MyTable

LOAD DATA INFILE..... INTO MyTable_Temp

UPDATE MyTable INNER JOIN 
MyTable_Temp
ON MyTable.ID=MyTable_Temp.ID
SET MyTable.Col1=MyTable_Temp.Col1, MyTable.Col2=MyTable_Temp.Col2.....

INSERT INTO MyTable(ID,Col1,Col2,...)
SELECT ID,Col1,Col2,... 
FROM MyTable_Temp
LEFT JOIN MyTable 
ON MyTable_Temp.ID = MyTable.ID
WHERE myTable.ID IS NULL

DROP TABLE MyTable_Temp

The syntax may not be exact, but this should give you the basics. Also, I know it's not pretty, but it gets the job done.

Update

I swapped the order of the insert and update, because doing insert first causes all the inserted rows to be updated when the update is called. If you do update first, only the existing records are updated. This should mean a little less work for the server, although the results should be the same.

Upvotes: 20

IAmTimCorey
IAmTimCorey

Reputation: 16757

If you were using Oracle or Microsoft SQL, you could use the MERGE. However, MySQL does not have a direct correlation to that statement. There is the single-row solution that you mentioned but, as you pointed out, it doesn't do bulk very well. Here is a blog post I found on the difference between Oracle and MySQL and how to do what Oracle does with MERGE in MySQL:

http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/

It isn't a pretty solution and it probably isn't as full a solution as you would like, but I believe that is the best there is for a solution.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

Try adding an insert trigger that does a pre-flight check and cancels the insert on duplicate key (after updating the existing row).

Not sure it'll scale well for bulk inserts, let alone work for load data infile, but it's the best I can think of. :-)

Upvotes: 0

Related Questions