Update an existing sql table based on content from an external csv file?

Question

I have a table like this:

key name address

1 aaa **

2 abc **

3 bdc **

4 cda **

5 dda ** ...........

Now I have another flat file (tab-delimited csv file) like this:

name phone

abc **

dda **

aaa **

This flat file will only have part of the entries in the original table. I want to update the table based on the content from the flat file (basically doing a join). Currently I am thinking to wirte some php script to do that. Basically load that flat file as array and have php code do the actual search and update.

Is there any easier/better way to do this task? Someone suggested a tool named phpmyadmin but that doesn't seem to be able to handle this level of complex task.

thanks.

Answer 1

You can create a temporary table, load the file into it and then update your persistent table.

CREATE TEMPORARY TABLE tmp
(
   name varchar(255),
   phone varchar(255),
   address varchar(255)
   INDEX (name)
);

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tmp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(@name, @phone, @address)
SET
name = @name,
phone = @phone
address = @address;

If you want to ignore the first n lines of the csv add the line below after the line LINES TERMINATED.

IGNORE n LINES 

Finally, you add/update the rows to your table based on the temporary one.

Suppose you're gonna use the column name to identify if row in the csv already exists on the table, you can do something like this:

INSERT INTO tbl (name, phone, address)
SELECT name, phone, address FROM tmp WHERE name NOT IN (SELECT name FROM table);

And to update the existing rows, you can use an update with JOIN.

UPDATE tbl a 
JOIN tmp b ON a.name = b.name 
SET a.phone = b.phone,
    a.address = b.address;