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

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.