Sqlite3 UPDATE FROM (VALUES) syntax error-CodePudding

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 tired 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.

CodePudding user response：

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;