Is it possible to use a LOAD DATA INFILE type command to UPDATE rows in the db?

Question

Pseudo table:

 | primary_key | first_name | last_name | date_of_birth |
 | 1           | John Smith |           | 07/04/1982    |

At the moment *first_name* contains a users full name for many rows. The desired outcome is to split the data, so first_name contains "John" and last_name contains "Smith".

I have a CSV file which contains the desired format of data:

 | primary_key | first_name | last_name |
 | 1           | John       | Smith     |

Is there a way of using the LOAD DATA INFILE command to process the CSV file to UPDATE all rows in this table using the primary_key - and not replace any other data in the row during the process (i.e. date_of_birth)?

Answer 1

No. While LOAD DATA INFILE has a REPLACE option, it will actually replace the row in question - that is, delete the existing one and insert a new one.

If you configure your LOAD DATA INFILE to only insert certain columns all others will be set to their default values, not to values they currently contain.

Can you modify your CSV file to contain a bunch of UPDATE statements instead? Should be reasonably straightforward via some regex replaces.

Answer 2

In this situation I usually LOAD DATA INFILE to a temp table with identical structure. Then I do INSERT with ON DUPLICATE KEY UPDATE from the temp table to the real table. This allows for data type checking without wrecking your real table; it's relatively quick and it doesn't require fiddling with your .csv file.

Answer 3

There is a nice tutorial in PHP about that. Adapt accordingly to your needs.