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.

回答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;

回答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.