Update multiple rows with different values in a single SQL query

user_997633 0
Viewed 141977

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.

rows sqlite sql
8 Answers

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_27716290

In recent versions of SQLite (beginning from 3.24.0 from 2018) you can use the UPSERT clause. Assuming only existing datasets are updated having a unique id column, you can use this approach, which is similar to @LSerni's ON DUPLICATE suggestion:

INSERT INTO myTable (id, posX, posY) VALUES
  ( 1, 35, 565),
  ( 3, 89, 224),
  ( 6, 11, 456),
  (14, 87, 475)
ON CONFLICT (id) DO UPDATE SET
  posX = excluded.posX, posY = excluded.posY
user_4165420

Use a comma ","

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

To update a table with different values for a column1, given values on column2, one can do as follows for SQLite:

"UPDATE table SET column1=CASE WHEN column2<>'something' THEN 'val1' ELSE 'val2' END"
user_137941890
Related