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

Question

Pseudo table:

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

First_name currently contains the full username for many lines. The desired result is to split the data, so first_name contains "John" and last_name contains "Smith".

I have a CSV file that contains the desired data format:

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

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

Answer 1

Not. While LOAD DATA INFILE has the REPLACE parameter, it actually replaces the specified row, that is, removes the existing one and insert a new one.

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

Can you modify your CSV file to contain a bunch of UPDATE statements instead? Should be fairly simple with some regular expression.

Answer 2

In this situation, I usually LOAD DATA INFILE for the temp table with the same structure. Then I INSERT with ON DUPLICATE KEY UPDATE from the temp table to the real table. This allows you to check the data type without destroying the real table; It is relatively fast and does not require messing with your CSV file.