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.

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;