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
