导入包含许多记录的CSV - 更新许多行

I have table with about 100K item numbers :

我有大约100K项目编号的表格:

id, itemnumber,text,orignal_qty,counted_qty

Im now importing a CSV file with maybe 100K records :

我现在导入一个可能有100K记录的CSV文件:

itemnumber_in,qty

That contains a count of the actual stock - i then need now to update the "counted_qty" in the main database for each record in the CSV.

这包含实际库存的计数 - 我现在需要更新CSV中每个记录的主数据库中的“counting_qty”。

How would i go about that ? would i read each line in the file from start to finish and do a :

我该怎么做?我会从头到尾读取文件中的每一行并执行以下操作:

UPDATE maintable SET counted_qty = counted_qty + qty WHERE itemnumber_in = itemnumber

Or is there a smarter/faster way of doing this.

或者有更聪明/更快的方式来做到这一点。

1 个解决方案

#1

Read in the CSV file using LOAD DATA and then use a join to update itemnumbers:

使用LOAD DATA读入CSV文件,然后使用连接更新itemnumbers:

UPDATE itemnumbers a INNER JOIN import b
    ON a.itemnumber = b.itemnumber_in
SET a.counted_qty = a.counted_qty + b.qty

The nice thing about this approach is a record in itemnumbers will only be updated in the event that it matched something in the input CSV file.

关于这种方法的好处是,只有在输入CSV文件中匹配某些内容时,才会更新项目编号中的记录。

Update:

If you first want to aggregate your imported data by id number, then you can use a subquery in your UPDATE statement:

如果您首先要按ID号聚合导入的数据,则可以在UPDATE语句中使用子查询:

UPDATE itemnumbers a
INNER JOIN
(
    SELECT itemnumber_in, SUM(qty) AS qty
    FROM import
    GROUP BY itemnumber_in
) b
    ON a.itemnumber = b.itemnumber_in
SET a.counted_qty = a.counted_qty + b.qty

#1