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.
>Solution :
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;