LOAD DATA INFILE and ON DUPLICATE KEY UPDATE

Question

When using LOAD DATA INFILE, is there a way to get the same functionality as ON ON DUPLICATE KEY UPDATE for regular INSERT statements?

What I want to do: for each line of my file, if the line does not exist, a new line is added, otherwise the selected fields are updated.

My table has 5 columns: A, B, C, D and E. A is the primary key. Sometimes I have to insert new lines with all the values, but sometimes I have to update only B and C, for example. But the fact is, I want to rearrange all INSERT or UPDATE to the same file.

thanks

Answer 1

If you want to insert / update some fields, then you must load the data into an additional table, and then use the INSERT, UPDATE or INSERT ... SELECT + ON DUPLICATE KEY UPDATE statement to copy / modify the data; otherwise, the other fields will be set to NULL.

In this case, the REPLACE option in LOAD DATA INFILE will not help you.

---

Alternatively, you can use the Data Import Tool (CSV format) in dbForge Studio for MySQL (free express version), just select the Append / Update import and specify the field mapping in the data import wizard.