MySQL - CSV UPDATE (not INSERT) into existing table?

Question

I have a table with the following columns:

id | name | city

The table has 1000 entries in it.

I have a CSV file with a limited number of lines like this:

id,city
34,Denver
45,Kansas City
145,New York

Can I use LOAD DATA INFILE to import my CSV into my table but instead of inserting new entries, it updates rows based off the id column?

RolandoMySQLDBA · Answer 1 · 2012-01-27 19:43:13Z

REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.

Here is something else you can do.

Suppose your table is called name_city and it looks like this:

CREATE TABLE name_city
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

and you want to do the LOAD DATA INFILE.

Here are your steps to update the city based on id:

Step 01) Create another table to use for the import

CREATE TABLE name_city_import LIKE name_city;

Step 02) Drop the name column from the import table

ALTER TABLE name_city_import DROP COLUMN name;

Step 03) Perform the import into name_city_import

LOAD DATA INFILE 'somefile.csv' INTO TABLE name_city_import ...

Step 04) Perform an UPDATE JOIN

UPDATE name_city_import B
INNER JOIN name_city A USING (id)
SET A.city = B.city;

Step 05) Remove the import table

DROP TABLE name_city_import;

That's it.

Give it a Try !!!

What type of effect would this have on performance? Compared to inserting directly in the first table? To me this seems as a lot of steps, but if performance here is good, then number of steps is negligable.

a1ex07 · Answer 2 · 2012-01-27 19:08:00Z

up vote 0 down vote

If you have unique index on id or it's a primary key, you can add REPLACE : LOAD DATA INFILE 'file_name' REPLACE ....

answered Jan 27 '12 at 19:08

a1ex07
2,64529

1

How does this command know that the id field in the CSV file corresponds with the primary key id? – Jakobud Jan 27 '12 at 19:47

2 Answers