Update multiple rows with different values in a single SQL query

Question

I have a SQLite database with table myTable and columns id  posX  posY   The number of rows changes constantly  might increase or decrease   If I know the value of id for each row  and the number of rows  can I perform a single SQL query to update all of the posX and posY fields with different values according to the id   For example   --------------------- myTable   id   posX    posY  1      35     565 3      89     224 6      11     456 14     87     475 ---------------------   SQL query pseudocode   UPDATE myTable SET posX id     arrayX id   posY id     arrayY id       arrayX and  arrayY are arrays which store new values for the posX and posY fields    If  for example  arrayX and arrayY contain the following values   arrayX     20  30  40  50   arrayY     100  200  300  400         then the database after the query should look like this   --------------------- myTable   id   posX    posY  1      20     100 3      30     200 6      40     300 14     50     400 ---------------------   Is this possible  I m updating one row per query right now  but it s going to take hundreds of queries as the row count increases  I m doing all this in AIR by the way

User · Answer

Yes  you can do this  but I doubt that it would improve performances  unless your query has a real large latency   You could do    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         The total cost is given more or less by  NUM QUERIES     COST QUERY SETUP   COST QUERY PERFORMANCE    This way  you knock down a bit on NUM QUERIES  but COST QUERY PERFORMANCE goes up bigtime  If COST QUERY SETUP is really huge  e g   you re calling some network service which is real slow  then  yes  you might still end up on top   Otherwise  I d try with indexing on id  or modifying the architecture   In MySQL I think you could do this more easily with a multiple INSERT ON DUPLICATE KEY UPDATE  but am not sure  never tried

User · Answer

There s a couple of ways to accomplish this decently efficiently   First - If possible  you can do some sort of bulk insert to a temporary table   This depends somewhat on your RDBMS host language  but at worst this can be accomplished with a simple dynamic SQL  using a VALUES   clause   and then a standard update-from-another-table   Most systems provide utilities for bulk load  though  Second - And this is somewhat RDBMS dependent as well  you could construct a dynamic update statement   In this case  where the VALUES      clause inside the CTE has been created on-the-fly   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     According to the documentation  this should be valid SQLite syntax  but I can t get it to work in a fiddle

User · Answer

Something like this might work for you    UPDATE myTable SET        UPDATE myTable SET        UPDATE myTable SET        UPDATE myTable SET          If any of the posX or posY values are the same  then they could be combined into one query  UPDATE myTable SET posX  39  WHERE id IN  2   3   40

User · Answer

I could not make  Clockwork-Muse work actually  But I could make this variation work   WITH Tmp AS  SELECT   FROM  VALUES  id1  newsPosX1  newPosY1                                        id2  newsPosX2  newPosY2                                                                                                      idN  newsPosXN  newPosYN   d id  px  py    UPDATE t  SET posX    SELECT px FROM Tmp WHERE t id   Tmp id       posY    SELECT py FROM Tmp WHERE t id   Tmp id   FROM TableToUpdate t   I hope this works for you too

User · Answer

Use a comma  quot   quot  eg   UPDATE my table SET rowOneValue   rowOneValue   1  rowTwoValue    rowTwoValue      rowTwoValue    rowTwoValue            v   1  WHERE value

User · Answer

Try with  update tablet set  row  value  where id 0001     row  value2  where id 0002

[sql] Update multiple rows with different values in a single SQL query

Examples related to sql

Examples related to sqlite

Examples related to rows