主页 / dba / 问题 / 307592
Accepted
Sam Whited
Asked: 2022-02-17 11:28:29 +0800 CST

来自冲突的选择中的 Sqlite upsert 并不总是更新行

  • 772

假设使用以下内容构建数据库：

PRAGMA foreign_keys = ON;

CREATE TABLE foo (
  id   INTEGER PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,

  UNIQUE (name)
);
INSERT INTO foo (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE bar (
   id    INTEGER PRIMARY KEY NOT NULL,
   key   TEXT    NOT NULL,
   fooID INTEGER NOT NULL,

   FOREIGN KEY (fooID) REFERENCES foo(id) ON DELETE CASCADE,
   UNIQUE (key)
);

如果我想将一个值插入到表中bar（这里由'key'字符串表示，但在现实世界中它将被参数化，这将是$1并且'三'将是$2）使用foo我尝试过的名称字段如下所示：

INSERT INTO bar (key, fooID)
  SELECT 'key', foo.id FROM foo WHERE foo.name='three'
  ON CONFLICT (key) DO UPDATE SET fooID=excluded.id;

但它有时似乎只更新现有行，例如。在上面的人为示例中，如果我从使用“三”foo.name变为“二”，它会更新得很好，但如果我再次使用“三”运行，它不会更新。

sqlite> delete from bar
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|3
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='two' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|2
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|2

我尝试过的其他变体excluded.id要么不存在，要么不起作用。有人可以解释这里的行为，或者我可以如何在排除的行中打印列名列表，或者我可以调试它的另一种方式（或者甚至可能是一种更好的方式来执行我正在尝试执行的插入操作）？

sqlite upsert

1 个回答

  1. Best Answer

    这里的问题似乎来自对“排除”关键字的误解。该文档指出：

    要使用在约束没有失败的情况下将被插入的值，请添加特殊的“excluded”。列名的表限定符。

    本来要插入的值fooID当然是excluded.fooID，而不是excluded.id我在上面的例子中。我相信external.id指的是列的行 ID，在这种情况下，第三行的行 ID 应该是 2。

    所以我们最终得到的最终查询是：

    INSERT INTO bar (key, fooID)
  SELECT 'key', foo.id FROM foo WHERE foo.name='three'
  ON CONFLICT (key) DO UPDATE SET fooID=excluded.fooID;
    • 0

相关问题