导入CSV以仅更新表中的一列 [英] Import CSV to Update only one column in table

问题描述

我有一个表格，如下所示：

I have a table that looks like this:

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

此表中大约有800,000个条目。我收到了一个新的CSV文件，其中会更新每个产品的所有数量，例如：

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

我的问题是，如何导入此CSV仅更新基于产品的数量（unique）但是留下 sku ，部门和其他字段？我知道如何在PHP中通过循环遍历CSV并对每一行执行更新，但这似乎是低效的。

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.

推荐答案

您可以使用 载入资料INFILE 将800,000行数据批量加载到临时表中，然后使用多表 UPDATE 语法将现有表连接到临时表并更新数量值。

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.

例如：

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;

这篇关于导入CSV以仅更新表中的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！