One way: SET x=CASE..END (any SQL)
Yes, you can do this, but I doubt that it would improve performances, unless your query has a real large latency.
If the query is indexed on the search value (e.g. if id is the primary key), then locating the desired tuple is very, very fast and after the first query the table will be held in memory.
So, multiple UPDATEs in this case aren't all that bad.
If, on the other hand, the condition requires a full table scan, and even worse, the table's memory impact is significant, then having a single complex query will be better, even if evaluating the UPDATE is more expensive than a simple UPDATE (which gets internally optimized).
In this latter case, 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 on NUM_QUERIES (from N separate id's to 1), but COST_QUERY_PERFORMANCE goes up (about 3x in MySQL 5.28; haven't yet tested in MySQL 8).
Otherwise, I'd try with indexing on id, or modifying the architecture.
This is an example with PHP, where I suppose we have a condition that already requires a full table scan, and which I can use as a key:
// Multiple update rules
$updates = [
"fldA='01' AND fldB='X'" => [ 'fldC' => 12, 'fldD' => 15 ],
"fldA='02' AND fldB='X'" => [ 'fldC' => 60, 'fldD' => 15 ],
...
];
The fields updated in the right hand expressions can be one or many, must always be the same (always fldC and fldD in this case). This restriction can be removed, but it would require a modified algorithm.
I can then build the single query through a loop:
$where = [ ];
$set = [ ];
foreach ($updates as $when => $then) {
$where[] = "({$when})";
foreach ($then as $fld => $value) {
if (!array_key_exists($fld, $set)) {
$set[$fld] = [ ];
}
$set[$fld][] = $value;
}
}
$set1 = [ ];
foreach ($set as $fld => $values) {
$set2 = "{$fld} = CASE";
foreach ($values as $i => $value) {
$set2 .= " WHEN {$where[$i]} THEN {$value}";
}
$set2 .= ' END';
$set1[] = $set2;
}
// Single query
$sql = 'UPDATE table SET '
. implode(', ', $set1)
. ' WHERE '
. implode(' OR ', $where);
Another way: ON DUPLICATE KEY UPDATE (MySQL)
In MySQL I think you could do this more easily with a multiple INSERT ON DUPLICATE KEY UPDATE, assuming that id is a primary key keeping in mind that nonexistent conditions ("id = 777" with no 777) will get inserted in the table and maybe cause an error if, for example, other required columns (declared NOT NULL) aren't specified in the query:
INSERT INTO tbl (id, posx, posy, bazinga)
VALUES (id1, posY1, posY1, 'DELETE'),
...
ON DUPLICATE KEY SET posx=VALUES(posx), posy=VALUES(posy);
DELETE FROM tbl WHERE bazinga='DELETE';
The 'bazinga' trick above allows to delete any rows that might have been unwittingly inserted because their id was not present (in other scenarios you might want the inserted rows to stay, though).
For example, a periodic update from a set of gathered sensors, but some sensors might not have been transmitted:
INSERT INTO monitor (id, value)
VALUES (sensor1, value1), (sensor2, 'N/A'), ...
ON DUPLICATE KEY UPDATE value=VALUES(value), reading=NOW();
(This is a contrived case, it would probably be more reasonable to LOCK the table, UPDATE all sensors to N/A and NOW(), then proceed with INSERTing only those values we do have).
A third way: CTE (Any SQL)
This is conceptually almost the same as the INSERT MySQL trick. As written, it works in PostgreSQL 9.6:
WITH updated(id, posX, posY) AS (VALUES
(id1, posX1, posY1),
(id2, posX2, posY2),
...
)
UPDATE myTable
SET
posX = updated.posY,
posY = updated.posY
FROM updated
WHERE (myTable.id = updated.id);
