MySQL bulk INSERT or UPDATE

Question

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

Answer 1

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?

Answer 2

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.