1

Assuming a database built with the following:

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) );

If I want to insert into a value into the table bar (here represented by the 'key' string, but in the real world it would be parameterized and this would be $1 and the 'three' would be $2) using the name field from foo I tried something like the following:

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

But it only sometimes seems to update the existing row, eg. in the above contrived example if I go from using 'three' for foo.name to 'two' it updates fine, but if I then run again with 'three' it does not update.

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

Other variations on excluded.id that I've tried either don't exist or don't work. Can someone explain the behavior here, or possibly how I can print a list of column names in the excluded row or another way I might debug this (or possibly even a better way to do the insert I'm trying to do)?

Improve this question

1 Answer 1

Reset to default
1

The issue here appears to come from a misunderstanding of the "excluded" keyword. The documentation states:

To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name.

The value that would have been inserted into fooID is of course excluded.fooID, not excluded.id as I have in the example above. I believe external.id refers to the row ID of the column, which in this case would have been 2 for the third row.

So the final query we end up with is:

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