MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

These steps can be used to emulate this functionality:

1. Create a new temporary table.

   CREATE TEMPORARY TABLE temporary_table LIKE target_table;
   

2. Optionally, drop all indices from the temporary table to speed things up.

   SHOW INDEX FROM temporary_table;
   DROP INDEX `PRIMARY` ON temporary_table;
   DROP INDEX `some_other_index` ON temporary_table;
   

3. Load the CSV into the temporary table

   LOAD DATA INFILE 'your_file.csv'
   INTO TABLE temporary_table
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   (field1, field2);
   

4. Copy the data using ON DUPLICATE KEY UPDATE

   SHOW COLUMNS FROM target_table;
   INSERT INTO target_table
   SELECT * FROM temporary_table
   ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);
   

5. Remove the temporary table

   DROP TEMPORARY TABLE temporary_table;
   

Using SHOW INDEX FROM and SHOW COLUMNS FROM this process can be automated for any given table.

We can replace first (two steps) with below single query in the answer shared by Jan.

For steps 1 and 2 we can create new table with same reference structure and without any indexes.

CREATE TEMPORARY TABLE temporary_table SELECT * FROM target_table WHERE 1=0;

Instead of.

1. Create a new temporary table.

   CREATE TEMPORARY TABLE temporary_table LIKE target_table;
   

2. Optionally, drop all indices from the temporary table to speed things up.

   SHOW INDEX FROM temporary_table;
   DROP INDEX `PRIMARY` ON temporary_table;
   DROP INDEX `some_other_index` ON temporary_table;