6

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?

5

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

  • Very cool, I didn't know that you could use a join like this. This was the perfect solution and kept my "A" database's primary keys in-tact. I could see how this may be a little bit more painful if you need to make updates across many columns, though. – Xaero Degreaz Oct 17 '16 at 15:39
  • this seems a bit of overhead because of the creating of the new tmp table , isn't there a way of doing bulk update directly from csv file if i have the entire row details ? – junior_software Sep 23 at 11:50
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.