5

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

manash
  • 6,985
  • 12
  • 65
  • 125
  • could you give the structure of your destination table please? Also, there's something unclear. If the row already exists why would you update it? – Sebas Jun 21 '12 at 14:06
  • No, you can't. the load statement is for a straightforward "slurp in the whole file". Your only option is a 'replace' parameter to do a full record swap if a loaded row has a primary/unique key conflict with an existing record: http://dev.mysql.com/doc/refman/5.1/en/load-data.html – Marc B Jun 21 '12 at 14:13
  • I've edited the question. Concerning existing rows, because I want to update some of their fields but not all. – manash Jun 21 '12 at 14:13
  • @MarcB Replacing the record doesn't help me since I'll loose information present in the fields I don't want to update. – manash Jun 21 '12 at 14:16
  • Then don't use a load infile query - it can't do what you want. – Marc B Jun 21 '12 at 14:16
  • Hi Mickael, take a look at my answer to this question, it should help you: http://stackoverflow.com/questions/10253605/import-csv-to-update-only-one-column-in-table/10253773#10253773 – Ike Walker Jun 21 '12 at 14:17
  • @MarcB Which good alternatives are there? By good, I mean good performance. – manash Jun 21 '12 at 14:17
  • @IkeWalker I've looked at your solution, but I think the UPDATE will take a lot of time. If I'm going to the LOAD DATA INFILE direction, this is because I don't want my threads to block during the INSERT but here they will block during the UPDATE. – manash Jun 21 '12 at 14:23
  • @MickaelMarrache, you can do the updates in smaller chunks if you want, for example using a where clause to specify a range of primary key values on the update. – Ike Walker Jun 21 '12 at 14:36

1 Answers1

2

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.

Devart
  • 119,203
  • 23
  • 166
  • 186