Mettre à jour plusieurs lignes avec des valeurs différentes dans une seule requête SQL

j'ai une base de données SQLite avec table myTable et colonnes id,posX,posY. Le nombre de lignes change constamment (peut augmenter ou diminuer). Si je connais la valeur de id pour chaque ligne, et le nombre de lignes, puis-je faire une seule requête SQL pour mettre à jour tous les posX et posY champs avec des valeurs différentes selon l'id?

Par exemple:

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

id   posX    posY

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

requête SQL pseudo-code:

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

@arrayX et @arrayY sont des tableaux qui stockent de nouvelles valeurs pour le posX et posY champs.

Si, par exemple, arrayX et arrayY contenir les valeurs suivantes:

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

... alors la base de données après la requête devrait ressembler à ceci:

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

id   posX    posY

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

Est-ce possible? Je mets à jour une rangée par requête en ce moment, mais cela va prendre des centaines de requêtes que le nombre de rangées augmente. Je fais tout ça dans l'AIR au fait.

18
demandé sur alex 2012-07-19 19:12:58

4 réponses

il y a plusieurs façons d'y arriver décemment efficacement.

Premier

Si possible, vous pouvez faire une sorte de gros insert à une table temporaire. Cela dépend un peu de votre langage RDBMS/host, mais au pire cela peut être accompli avec un simple SQL dynamique (en utilisant un VALUES() clause), puis une mise à jour standard à partir d'une autre table. La plupart des systèmes fournissent des utilitaires pour la charge globale, Bien que

Deuxième

Et ce est quelque peu dépendant de RDBMS aussi bien, vous pourriez construire une déclaration de mise à jour dynamique. Dans ce cas, où l' VALUES(...) l'article à l'intérieur de la CCE a été créé à la volée:

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)

(selon la documentation, ceci devrait valide SQLite syntaxe, mais je ne peux pas le faire fonctionner dans un violon)

20
répondu Clockwork-Muse 2015-07-17 01:12:17

Oui, vous pouvez le faire, mais je doute que cela permettrait d'améliorer les performances, à moins que votre requête a un vrai gros temps de latence.

Tu pourrais faire:

 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]...);

le coût total est donné plus ou moins par: NUM_QUERIES * ( COST_QUERY_SETUP + COST_QUERY_PERFORMANCE ). De cette façon, vous frappez un peu sur NUM_QUERIES, mais COST_QUERY_PERFORMANCE monte en puissance. Si COST_QUERY_SETUP est vraiment énorme (par exemple, vous appelez un service réseau qui est très lent) alors, oui, vous pouvez toujours fin sur le dessus.

sinon, j'essaierais d'indexer sur id, ou de modifier l'architecture.

dans MySQL je pense que vous pourriez le faire plus facilement avec un INSERT multiple sur la mise à jour de la clé dupliquée (mais je ne suis pas sûr, jamais essayé).

15
répondu LSerni 2012-07-19 15:33:09

quelque Chose comme ça pourrait fonctionner pour vous:

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

si l'une des valeurs posX ou posY est la même, alors elles peuvent être combinées en une seule requête

UPDATE myTable SET posX='39' WHERE id IN('2','3','40');
5
répondu Brian 2012-07-20 19:56:32

essayez avec " update tablet set (row='value' où id=0001'), (row='value2' où id=0002'), ...

-10
répondu user2952108 2013-11-04 10:05:35