CODEWITHSUNDEEP
mysqlcsvimport
Ryan Kempt
Ryan Kempt

Reputation: 4209

Import CSV to Update only one column in table

I have a table that looks like this:

products
--------
id, product, sku, department, quantity

There are approximately 800,000 entries in this table. I have received a new CSV file that updates all of the quantities of each product, for example:

productA, 12
productB, 71
productC, 92

So there are approximately 750,000 updates (50,000 products had no change in quantity).

My question is, how do I import this CSV to update only the quantity based off of the product (unique) but leave the sku, department, and other fields alone? I know how to do this in PHP by looping through the CSV and executing an update for each single line but this seems inefficient.

Upvotes: 44

Views: 65194

Answers (3)

Gruber
Gruber

Reputation: 2298

Answer from @ike-walker is indeed correct but also remember to double check how your CSV data if formatted. Many times for example CSV files can have string fields enclosed in double quotes ", and lines ending with \r\n if working on Windows.
By default is assumed that no enclosing character is used and line ending is \n. More info and examples here https://mariadb.com/kb/en/importing-data-into-mariadb/

This can be fixed by using additional options for FIELDS and LINES

CREATE TEMPORARY TABLE your_temp_table LIKE your_table;

LOAD DATA INFILE '/tmp/your_file.csv'
INTO TABLE your_temp_table
FIELDS 
   TERMINATED BY ','            
   OPTIONALLY ENCLOSED BY '"'    -- new option
LINES TERMINATED BY '\r\n'       -- new option

(id, product, sku, department, quantity); 

UPDATE your_table
INNER JOIN your_temp_table on your_temp_table.id = your_table.id
SET your_table.quantity = your_temp_table.quantity;

DROP TEMPORARY TABLE your_temp_table;

Upvotes: 1

DocJones
DocJones

Reputation: 677

I would load the update data into a seperate table UPDATE_TABLE and perform an update within MySQL using:

UPDATE PRODUCTS P SET P.QUANTITY=(
    SELECT UPDATE_QUANTITY
    FROM UPDATE_TABLE
    WHERE UPDATE_PRODUCT=P.PRODUCT
)

I dont have a MySQL at hand right now, so I can check the syntax perfectly, it might be you need to add a LIMIT 0,1 to the inner SELECT.

Upvotes: 5

Ike Walker
Ike Walker

Reputation: 65537

You can use LOAD DATA INFILE to bulk load the 800,000 rows of data into a temporary table, then use multiple-table UPDATE syntax to join your existing table to the temporary table and update the quantity values.

For example:

CREATE TEMPORARY TABLE your_temp_table LIKE your_table;

LOAD DATA INFILE '/tmp/your_file.csv'
INTO TABLE your_temp_table
FIELDS TERMINATED BY ','
(id, product, sku, department, quantity); 

UPDATE your_table
INNER JOIN your_temp_table on your_temp_table.id = your_table.id
SET your_table.quantity = your_temp_table.quantity;

DROP TEMPORARY TABLE your_temp_table;

Upvotes: 121

Related Questions