UPSERT documentation question

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

UPSERT documentation question

nomad
The page https://sqlite.org/lang_UPSERT.html includes the following
text:

    Some examples will help illustrate the difference:

        CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
        INSERT INTO vocabulary(word) VALUES('jovial')
          ON CONFLICT(word) DO UPDATE SET count=count+1;

    The upsert above inserts the new vocabulary word "jovial" if that
    word is not already in the dictionary, or if it is already in the
    dictionary, it increments the counter. The "count+1" expression
    could also be written as "vocabulary.count". ...

Shouldn't that actually be written as "vocabulary.count+1"?

--
Mark Lawrence
_______________________________________________
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: UPSERT documentation question

David Raymond
> CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
> INSERT INTO vocabulary(word) VALUES('jovial')
> ON CONFLICT(word) DO UPDATE SET count=count+1;
>
> Shouldn't that actually be written as "vocabulary.count+1"?

Nope. Unqualified names there refer to the one and only record that's getting updated.

Similar to how in an blanket update statement you would do:
update vocabulary set count = count + 1;
...and not:
update vocabulary set vocabulary.count = vocabulary.count + 1;

I mean, it might still work, but it's not needed, no.

_______________________________________________
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: UPSERT documentation question

nomad
On Fri Feb 07, 2020 at 01:45:53PM +0000, David Raymond wrote:
> > CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT
> > 1); INSERT INTO vocabulary(word) VALUES('jovial') ON CONFLICT(word)
> > DO UPDATE SET count=count+1;
> >
> > Shouldn't that actually be written as "vocabulary.count+1"?
>
> Nope. Unqualified names there refer to the one and only record that's
> getting updated.

Your edit of my email broke some context. I was actually referring to
the paragraph after the example SQL, where "vocabularly.count" was
given as being equivalent to "count+1".

> Similar to how in an blanket update statement you would do:
> update vocabulary set count = count + 1;
> ...and not:
> update vocabulary set vocabulary.count = vocabulary.count + 1;
>
> I mean, it might still work, but it's not needed, no.

It is needed if you are have a correllated subquery in the UPDATE
statement and want to refer to the original row.

--
Mark Lawrence
_______________________________________________
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: UPSERT documentation question

nomad
I should perhaps point out that the issue has been solved - the page
has been adjusted. Thanks devs.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users