Обновление нескольких строк с разными значениями в одном запросе SQL

У меня есть база данных SQLite с таблицей myTable и столбцом id, posX, posY. Количество строк постоянно изменяется (может увеличиваться или уменьшаться). Если я знаю значение id для каждой строки и количество строк, могу ли я выполнить один SQL-запрос для обновления всех полей posX, posY с разными значениями в соответствии с идентификатором?

Пример:

---------------------
myTable:

id   posX    posY

1      35     565
3      89     224
6      11     456
14     87     475
---------------------

Псевдокод для SQL-запроса:

" UPDATE myTable SET posX[id] = @arrayX[id], posY[id] = @arrayY[id] "

@arrayX, arrayY - массивы, которые сохраняют новые значения для поля posX и posY.

если, например, arrayX и arrayY содержат следующие значения:

arrayX = { 20, 30, 40, 50 }
arrayY = { 100, 200, 300, 400 }

база данных после запроса должна выглядеть так:

---------------------
myTable:

id   posX    posY

1      20     100
3      30     200
6      40     300
14     50     400
---------------------

Возможно ли это? Я обновляю одну строку для каждого запроса прямо сейчас. Но он будет принимать сотни запросов по мере увеличения количества строк. Кстати, я делаю все это в AIR.

Ответ 1

Есть несколько способов сделать это прилично эффективно.

Первый -
Если возможно, вы можете сделать какую-то объемную вставку во временную таблицу. Это в некоторой степени зависит от вашего RDBMS/хост-языка, но в худшем случае это может быть выполнено с помощью простого динамического SQL (с использованием предложения VALUES()), а затем стандартного файла update-from-another-table. Большинство систем предоставляют утилиты для массовой загрузки, хотя

Второе -
И это тоже зависит от СУБД, вы можете создать динамическое выражение о обновлении. В этом случае, когда предложение VALUES(...) внутри CTE было создано "на лету":

WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1), 
                               (id2, newsPosX2, newPosY2),
                               ......................... ,
                               (idN, newsPosXN, newPosYN))

UPDATE TableToUpdate SET posX = (SELECT px
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id),
                         posY = (SELECT py
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id)


WHERE id IN (SELECT id
             FROM Tmp)

(Согласно документации, это должен быть допустимый синтаксис SQLite, но я не могу заставить его работать в скрипке)

Ответ 2

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

Вы можете сделать:

 UPDATE table SET posX=CASE
      WHEN id=id[1] THEN posX[1]
      WHEN id=id[2] THEN posX[2]
      ...
      ELSE posX END, posY = CASE ... END
 WHERE id IN (id[1], id[2], id[3]...);

Общая стоимость предоставляется более или менее: NUM_QUERIES * (COST_QUERY_SETUP + COST_QUERY_PERFORMANCE). Таким образом, вы немного сбиваете NUM_QUERIES, но COST_QUERY_PERFORMANCE увеличивается в разное время. Если COST_QUERY_SETUP действительно огромна (например, вы вызываете некоторую сетевую услугу, которая очень медленная), тогда да, вы все равно можете оказаться наверху.

В противном случае я попытался бы с индексированием на id или изменением архитектуры.

В MySQL я думаю, что вы могли бы сделать это более легко с помощью нескольких INSERT ON DUPLICATE KEY UPDATE (но я не уверен, никогда не пробовал).

Ответ 3

Что-то вроде этого может сработать для вас:

"UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;
 UPDATE myTable SET ... ;"

Если любое из значений posX или posY одинаково, их можно объединить в один запрос

UPDATE myTable SET posX='39' WHERE id IN('2','3','40');

Ответ 4

Попробуйте установить "планшет для обновления" (row = 'value' где id = 0001 '), (строка =' значение2 ', где id = 0002'),...