Import CSV to Update only one column in table

Question

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.

Churchless

Answer 1

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;

Kauffman

@lke Walker, Can you answer my this question #21496100 – Sheets

Rather than the 'LOAD DATA' section, you can use phpMyAdmin's "Import" function if you prefer, then use the 'UPDATE' section of this answer. – Extrusive

With the above answer does it matter if the temporary table data does not match exactly the master table data? So, for example if the CSV file contains 50,000 rows of data but the master table contains 200,000 rows of data. Will this method just ignore rows which don't have a match on the column id? Also, what if the CSV file contains new rows? Is it possible to also add new rows using the above function in a modified way? – Yugoslavia

I used LOAD DATA LOCAL INFILE to send to a remote server (e.g Amazon RDS) – Overleap

Answer 2

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.

Expulsive

Answer 3

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;

Godin