Re: [sqlite] How to use WHERE clause in UPSERT's conflict target

[nomad]

On Wed Jun 20, 2018 at 05:26:19PM -0400, Richard Hipp wrote:
> On 6/20/18, Jonathan Koren  wrote:
> >
> > The grammar & documentation
> >  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:
> 
> The WHERE clause on the conflict-target is only used for partial
> indexes.

It would be useful and sensible for SQLite to complain loudly when it
parses a query with a conflict target that doesn't exactly match a
known constraint. Otherwise it is clearly easy for authors to write
legal but misleading queries with actions quite different to the
obvious intention.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use WHERE clause in UPSERT's conflict target

[Richard Hipp]

On 6/20/18, Jonathan Koren  wrote:
>
> The grammar & documentation
>  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:

The WHERE clause on the conflict-target is only used for partial indexes.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use WHERE clause in UPSERT's conflict target

[Jonathan Koren]

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   guidcontent
--  --  --
1   a1  foo
2   b2  bar


The grammar & documentation
 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   guidcontent
--  --  --
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   guidcontent
--  --  --
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