Sqlite3 UPDATE FROM(VALUES)语法错误

Question

我正在尝试使用一个查询来用键值对更新表中的行。因此，列值为键的行将使用值更新
为此，我尝试使用：

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;

和

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;

在这两种情况下，我都得到：

接近"；("：语法错误(1(

我的错误在哪里？提前谢谢。

Answer 1

SQLite不支持来自使用VALUES的子查询的别名列

您可以使用别名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;

或者，更简单地说，使用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;