Re: [sqlite] UPSERT documentation question
I should perhaps point out that the issue has been solved - the page has been adjusted. Thanks devs. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT documentation question
On Fri Feb 07, 2020 at 01:45:53PM +, 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT documentation question
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT documentation question
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
