LOAD DATA INFILE and ON DUPLICATE KEY UPDATE

Question

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

What I want to do is: for each line of my file, if the row doesn't exist, a new row is inserted, 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 rows with all the values, but sometimes I have to update only B and C, for example. But, the point is that I want to regroup all the INSERT or UPDATE in the same file.

Thanks

Answer 1

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

The REPLACE option in LOAD DATA INFILE won't help you in this case.

---

Also, you can use Data Import tool (CSV format) in dbForge Studio for MySQL (free express edition), just choose Append/Update import mode and specify fields mapping in the Data Import wizard.