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

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.

Use a comma ","

eg: 
UPDATE my_table SET rowOneValue = rowOneValue + 1, rowTwoValue  = rowTwoValue + ( (rowTwoValue / (rowTwoValue) ) + ?) * (v + 1) WHERE value = ?

Try with "update tablet set (row='value' where id=0001'), (row='value2' where id=0002'), ...

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!

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');

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