UPSERT 用于“INSERT INTO tab SELECT * FROM another_tab"

2022-01-17 00:00:00 sql-update sql select sqlite sql-insert

我会怎么做UPSERT"?(INSERT OR UPDATE) 插入到 SQLite 表中时从另一个表中插入多行.

How would I do "UPSERT" (INSERT OR UPDATE) into SQLite table when inserting multiple rows from another table.

我试过了:

INSERT INTO tab_name
SELECT * FROM tmp
ON CONFLICT(id)
    DO UPDATE SET
      val = excluded.val;

但它给了我:

DO"附近的语法错误

实现这一目标的正确和最有效的方法是什么?

What would be the correct and the most efficient way to achieve that?

推荐答案

您可能遇到了一个名为 的记录陷阱解析歧义 :

You might have hit a documented trap called parsing ambiguity :

当 UPSERT 附加到的 INSERT 语句从 SELECT 语句中获取其值时，可能存在解析歧义.解析器可能无法判断 "ON" 关键字是否引入了 UPSERT 或者它是否是连接的 ON 子句.要解决此问题，SELECT 语句应始终包含 WHERE 子句，即使该 WHERE 子句只是 "WHERE true".)

When the INSERT statement to which the UPSERT is attached takes its values from a SELECT statement, there is a potential parsing ambiguity. The parser might not be able to tell if the "ON" keyword is introducing the UPSERT or if it is the ON clause of a join. To work around this, the SELECT statement should always include a WHERE clause, even if that WHERE clause is just "WHERE true".)

那么，这样效果更好吗?

So, does this work better?

INSERT INTO tab_name
SELECT * FROM tmp WHERE true
ON CONFLICT(id) DO UPDATE SET val = excluded.val;

相关文章