Sqlite3 UPDATE FROM (VALUES) syntax error

Question

I'm trying to use a query for updating rows in my table with key-value pairs. So the row where column-value is key updates with value.
For this I tried to use:

UPDATE access_keys 
SET global_id=a.global_id 
FROM access_keys 
INNER JOIN (VALUES (1,123),(2,321)) as a(id, global_id) 
on a.id = access_keys.id WHERE a.id = access_keys.id;

And

UPDATE access_keys 
SET global_id=a.global_id 
FROM (VALUES (1,123),(2,321)) as a(id, global_id) 
WHERE a.id = access_keys.id;

In both cases I get:

near "(": syntax error (1)

Where is my error? Thanks in advance.

Answer 1

SQLite does not support aliasing columns that come from a subquery using VALUES.

You can use the aliases column1, column2:

UPDATE access_keys AS ak 
SET global_id = v.column2 
FROM (VALUES (1, 123),(2, 321)) AS v
WHERE v.column1 = ak.id;

Or, simpler, use a CTE:

WITH cte(id, global_id) AS (VALUES (1, 123),(2, 321))
UPDATE access_keys AS ak 
SET global_id = c.global_id 
FROM cte AS c
WHERE c.id = ak.id;