Menu
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.
549
ON DUPLICATE KEY UPDATE clause and UPDATE with JOIN() function to update multiple columns in multiple rows with different values in MySQL.
2 Answers. Show activity on this post. UPDATE mytable SET fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END, drink = CASE WHEN id=1 THEN 'water' ELSE 'wine' END, food = CASE WHEN id=1 THEN 'pizza' ELSE 'fish' END WHERE id IN (1,2);
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
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)
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
