LOAD DATA INFILE

[elijah06]

Hello folks,

Is it possible to update a column using LOAD DATA INFILE, So the following is an example:

LOAD DATA LOCAL INFILE 'c:/test.txt'
INTO TABLE test
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(lname, fname, zip);

Results:

id | fname | lname | zip
1 | kurt | russell | 45678
2 | worth | tim | 45687
3 | ridd | sherly | 45698

Now, I would like to load a file into the test table to replace only the zip column with the following:

LOAD DATA LOCAL INFILE 'c:/test.txt'
REPLACE INTO TABLE test
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(zip);

But the above query removes the current data and sets only the zip and removes id, fname, lname. I would like it to update the zip column only. Is it possible?

Thank you,

Dan

 

[towerbase]

I don't think that you can use LOAD DATA LOCAL INFILE to do precisely what you want to do.

I guess the main reason why the command that you want is not implemented is that in an SQL table the rows cannot be assumed to be in any particular order. How would LOAD DATA know which row to apply the replacement zip code?

LOAD DATA needs a unique key defined in the table and present in the text file so that it can REPLACE rows. I guess in your example, the unique key would be PRIMARY KEY(fname,lname).

Even if you define a PRIMARY KEY and include it in your data, LOAD DATA LOCAL INFILE will replace the whole row and not just update the zip code. The way around this is to load your data into a temporary table and then update the zip codes in your main table from the temporary table.

Andrew
Hampshire, UK