來自衝突的選擇中的 Sqlite upsert 並不總是更新行

假設使用以下內容建構數據庫：

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要麼不存在，要麼不起作用。有人可以解釋這裡的行為，或者我可以如何在排除的行中列印列名列表，或者我可以調試它的另一種方式（或者甚至可能是一種更好的方式來執行我正在嘗試執行的插入操作）？

這裡的問題似乎來自對“排除”關鍵字的誤解。該文件指出：

要使用在約束沒有失敗的情況下將被插入的值，請添加特殊的“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;

引用自：https://dba.stackexchange.com/questions/307592