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, posY fields with different values according to the id?
example:
---------------------
myTable:
id posX posY
1 35 565
3 89 224
6 11 456
14 87 475
---------------------
Pseudo code for the SQL query:
" UPDATE myTable SET posX[id] = @arrayX[id], posY[id] = @arrayY[id] "
@arrayX, arrayY being arrays which store new values for the posX and posY field.
if, for example arrayX and arrayY contain following values:
arrayX = { 20, 30, 40, 50 }
arrayY = { 100, 200, 300, 400 }
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.
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)