How to use WHERE clause in UPSERT's conflict target

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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       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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 6/20/18, Jonathan Koren <[hidden email]> wrote:
>
> 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:

The WHERE clause on the conflict-target is only used for partial indexes.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
> >
> > 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:
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users