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

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?

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 !!!

StackExchange Database Administrators Q#11811, answer score: 7