Update multiple rows with different values in a single SQL query

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 (PostgreSQL, not sure about SQLite3)

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);

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)