How to make a MASS UPDATE SQL query IN ONE ROW?

Question

MySQL

There is a problem. I am updating 30k+ rows in a loop. And it takes 40+ minutes.
That is, I run the following query in a loop:

UPDATE product 
         SET price = $product['price'],
             price_retail = $product['price_retail'] 
    WHERE upc = $product['supplier'] AND sku = $product['uniqCode']

And this happens 30 thousand times.
How can this be done in one request? I've seen examples with Update ... case.. when... but couldn't figure out how to apply it in my case.

Answer 1

1. You can do this in one query by using an intermediate table. First, load the data from the file into this table into the database, and then run update with one query. Do not forget to collect statistics on the table after loading data into it from a file.
2. Without an auxiliary table: you need an index on the fields sku, upc. But it won't be in one request.

Answer 2

Make a unique key for (upc, sku). Then

INSERT INTO `product` (`upc`, `sku`, `price`, `price_retail`)
  VALUES (:upc1, :sku1, :price1, :price_retail1), ..., 
         (:upcN, :skuN, :priceN, :price_retailN)
  ON DUPLICATE KEY UPDATE `price` = VALUES(`price`), 
    `price_retail` = VALUES(`price_retail`)

Just watch the query string length, max_allowed_packet
variable Another option is to use a prepared query. Prepare and bind are done once before the loop, then execute at each step. Savings go to query compilation.

Answer 3

Your option: https://stackoverflow.com/questions/11664684/how-t...

Answer 4

Bulk Insert - just for bulk data filling

Answer 5

And where does the data for update come from?
If from other tables or regular data, then all this is simply done in the form of a "one-line" action (update) at a time.