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.

Answer 1

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)