Как сделать МАССОВЫЙ UPDATE SQL запрос В ОДНУ СТРОКУ?

Question

[Олег]

Есть проблема. У меня в цикле идет обновление 30к+ строк. И тратится на это 40+ минут.
То есть у меня в цикле выполняется такой запрос:

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

И так происходит 30 тыс раз.
Как можно это сделать в один запрос? Я видел примеры с Update ... case.. when... но так и не смог понять, как это применить в моем случае.

Answer 1

[TheRonCronix]

1. Сделать это в один запрос можно путем использования промежуточной таблицы. Сначала загрузите в БД данные из файла в эту таблицу, а затем одним запросом проведите update. Не забудьте собрать статистику на таблице после загрузки данных в нее из файла.
2. Без вспомогательной таблицы: вам нужен индекс на поля sku, upc. Но это будет не в один запрос.

Answer 2

[Rsa97]

Делайте уникальный ключ по (upc, sku). Затем

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`)

Только следите за длиной строки запроса, переменная max_allowed_packet

Ещё вариант - использовать подготовленный запрос. Один раз перед циклом делается prepare и bind, затем на каждом шаге execute. Экономия идёт на компиляции запроса.

Comments

[Олег]

Спасибо за ответ. Но данное решение не подходит, так как если нет совпадения, он добавляет данные в таблицу. Мне же нужно ТОЛЬКО обновить.

[Rsa97]

bysobi, Тогда только подготовленный запрос

Answer 3

[Дмитрий Ким]

Ваш вариант: https://stackoverflow.com/questions/11664684/how-t...

Comments

[Олег]

Дмитрий, спасибо за ответ. Да, я находил данное решение. В описании я указал, что пытался его использовать, но не смог подстроить под свой пример.

[Дмитрий Ким]

Думаю, примерно так (естественно, лучше экранировать значения):

UPDATE `product`
SET `price` = (
  CASE
    WHEN (`upc` = $product['suppliers'][1] AND `sku` = $product['uniqCodes'][1]) THEN $product['prices'][1]
    WHEN (`upc` = $product['suppliers'][2] AND `sku` = $product['uniqCodes'][2]) THEN $product['prices'][2]
    -- ...
    WHEN (`upc` = $product['suppliers'][99] AND `sku` = $product['uniqCodes'][99]) THEN $product['prices'][99]
  END),
SET `price_retail` = (
  CASE
    WHEN (`upc` = $product['suppliers'][1] AND `sku` = $product['uniqCodes'][1]) THEN $product['price_retails'][1]
    WHEN (`upc` = $product['suppliers'][2] AND `sku` = $product['uniqCodes'][2]) THEN $product['price_retails'][2]
    -- ...
    WHEN (`upc` = $product['suppliers'][99] AND `sku` = $product['uniqCodes'][99]) THEN $product['price_retails'][99]
  END),
WHERE `id` IN(
    $product['ids'][1],
    $product['ids'][2],
    -- ...
    $product['ids'][99],
    );

[Олег]

Спасибо большое! Я сейчас попробую и обязательно отпишусь

[Олег]

Дмитрий Ким, прошу прощения за глупый вопрос, если бы я знал как делать, не задавал бы их.
У меня нету id продукта. В моем файле есть лишь эти данные: Скрин
По поставщику + уникальный код я нахожу нужный мне продукт и обновляю им цены. Ids у меня нету и быть не может.
Как быть в этой ситуации?

[Дмитрий Ким]

UPDATE `product`
SET `price` = (
  CASE
    WHEN (`upc` = $product['suppliers'][1] AND `sku` = $product['uniqCodes'][1]) THEN $product['prices'][1]
    WHEN (`upc` = $product['suppliers'][2] AND `sku` = $product['uniqCodes'][2]) THEN $product['prices'][2]
    -- ...
    WHEN (`upc` = $product['suppliers'][99] AND `sku` = $product['uniqCodes'][99]) THEN $product['prices'][99]
  END),
SET `price_retail` = (
  CASE
    WHEN (`upc` = $product['suppliers'][1] AND `sku` = $product['uniqCodes'][1]) THEN $product['price_retails'][1]
    WHEN (`upc` = $product['suppliers'][2] AND `sku` = $product['uniqCodes'][2]) THEN $product['price_retails'][2]
    -- ...
    WHEN (`upc` = $product['suppliers'][99] AND `sku` = $product['uniqCodes'][99]) THEN $product['price_retails'][99]
  END)

Answer 4

[InoMono]

Bulk Insert - как раз для массового залития данных

Comments

[Олег]

Спасибо за ответ. Насколько я понял, мне этот пример не подходит, потому что мне не нужно вставлять данные, если их нет в таблице, а ТОЛЬКО обновлять имеющиеся. Поправьте, пожалуйста, если я не прав.

Answer 5

[d-stream]

А откуда берутся данные для update?
Если из других таблиц или регулярных данных - то все это элементарно делается в виде "однострочного" действия (update) за один раз.

Comments

[Олег]

Спасибо за ответ! Данные берутся с xls файла. Я паршу через либу и открываю по 100 строк за раз. Образуется массив данных, этот массив проганяю через foreach. В цикле и делаю Update

[d-stream]

bysobi, не скажу про MySQL, но в MSSQL я в таких случаях пользую select ... from openrowset

к примеру для вариантов txt и mdb прайсов на почти миллион строк процесс "переливки" занимает 1-2 минуты на средненьких компьютерах/серверах