Как одним запросом обновить несколько строк одним запросом с формулами SQL?

Question

[Vetenskap]

Есть таблица с 600к позиций. Имеет вид (id, article, stock, stock2, sumStock, price).
Есть массив PHP размером в 200к позиций. Имеет вид (art (который соответствует article из первой таблицы), st (который должен вставиться в stock2 в первую таблицу), pr (тоже должен вставиться в первую таблицу).
Манипуляции должны производится такие: по article из массива идёт поиск строки в БД. По нахождению из массива в строку должно вставиться в stock2 значение. Потом stock и stock2 складываются и результат вносится в sumStock. Price соответственно тоже должен вставиться из массива в строку БД. И со всеми 200к позиций из массива должны произойти манипуляции с БД. Как сделать это быстро ? Можно ли всё это сделать как-то через один запрос в БД и используя транзакции ?

Comments

[Akina]

Создать временную таблицу.
Вставить в неё данные из массива.
Проиндексировать.
Обновить данные в рабочей таблице.

Если данные в массиве взяты из внешнего файла, пригодного к импорту в MySQL - то импортировать сразу во временную таблицу, минуя массив. Файл желательно поместить на диск MySQL-сервера.

Если нет - рассмотреть возможность сброса данных из массива в CSV (совсем прекрасно - если в файл на MySQL-сервере) и последующий импорт во временную таблицу из него.

[Vetenskap]

Akina, Допустим я создам временную таблицу, а какой вид SQL запроса должен быть, не подскажете, пожалуйста ?

[Akina]

LOAD DATA Statement

Читайте внимательно - там есть заморочки с FILE permission, @@secure_file_priv и @@local_infile.

Answer 1

[Gnifajio None]

Да, это можно сделать через один запрос с использованием транзакций. Для этого можно использовать оператор UPDATE с формулами в качестве новых значений для столбцов.

Примерно так:

BEGIN;
UPDATE table1
SET stock2 = array.st,
    sumStock = stock + stock2,
    price = array.pr
FROM (VALUES ('art1', 'st1', 'pr1'), ('art2', 'st2', 'pr2'), ...) AS array(art, st, pr)
WHERE table1.article = array.art;
COMMIT;

В этом запросе сначала начинается транзакция с командой BEGIN. Затем обновляется таблица table1 с использованием оператора UPDATE. В качестве новых значений указываются формулы, которые берут значения из массива с помощью FROM и VALUES. С помощью условия WHERE задается, что строки в таблице table1 должны быть обновлены только для тех строк, у которых article совпадает с art из массива. В конце транзакция завершается командой COMMIT.