CODEWITHSUNDEEP
mysqlsqldatabase
thorway
thorway

Reputation: 25

Importing a CSV with many records - updating many rows

I have table with about 100K item numbers :

id, itemnumber,text,orignal_qty,counted_qty

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

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.

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.

Upvotes: 1

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

Read in the CSV file using LOAD DATA and then use a join to update 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.

Update:

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

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

Upvotes: 1

Related Questions