Hello sqlite-users, I am trying out the new UPSERT feature introduced in 3.24.0 and ran into something I don't quite understand. First some setup:
CREATE TABLE notes( guid TEXT UNIQUE NOT NULL, content TEXT ); INSERT INTO notes (guid, content) VALUES ('a1', 'foo'), ('b2', 'bar') ; SELECT rowid, * FROM notes; rowid guid content ---------- ---------- ---------- 1 a1 foo 2 b2 bar The grammar & documentation <https://www.sqlite.org/draft/lang_UPSERT.html> shows a WHERE clause can be given in the "conflict target" of the UPSERT, but the documentation does not explain how the result of this clause impacts the statement. As a test, I tried the following: *-- test #1* INSERT INTO notes (guid, content) VALUES ('b2', 'TEST') ON CONFLICT (guid) *where 1* DO UPDATE SET content = excluded.content; ; SELECT rowid, * FROM notes; rowid guid content ---------- ---------- ---------- 1 a1 foo 2 b2 *TEST* *-- row was updated* *-- test #2* INSERT INTO notes (guid, content) VALUES ('b2', 'TEST AGAIN') ON CONFLICT (guid) *where 0* DO UPDATE SET content = excluded.content; ; SELECT rowid, * FROM notes; rowid guid content ---------- ---------- ---------- 1 a1 foo 2 b2 *TEST AGAIN* *-- row was updated again* At least in this case, there appears to be no difference between a truth-y and false-y result of that WHERE clause. Shouldn't there be a difference? What am I not understanding about this? Thanks, Jonathan Koren _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users