MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

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;
   

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.