Re: [sqlite] 3.24 draft - upsert
Per Keith Medcalf's comment on the ancient master file merge, I found this inscription concerning batch processing in the elephant's graveyard: https://www.ibm.com/support/knowledgecenter/zosbasics/com.ibm.zos.zconcepts/zconc_batchscen2.htm Jim Callahan On Wed, May 9, 2018 at 10:06 AM, Keith Medcalf wrote: > > This is a baby implementation of the master file merge from the early part > of the last century (after the stone knives but somewhat before > bearskins). > > Take two tables, one mounted on tape drive A, with output to tape drive B, > updated from a transaction file on tape drive C. Start Friday night. Come > Monday morning your master is now on drive B and up-to-date. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp > >Sent: Wednesday, 9 May, 2018 03:48 > >To: SQLite mailing list > >Subject: Re: [sqlite] 3.24 draft - upsert > > > >On 5/9/18, Olivier Mascia wrote: > >> About: > >> > >> "Column names in the expressions of a DO UPDATE refer to the > >original > >> unchanged value of the column, before the attempted INSERT. To use > >the value > >> that would have been inserted had the constraint not failed, add > >the special > >> "excluded." table qualifier to the column name." > >> > >> Why using 'excluded' wording for this? > > > >Because that is what PostgreSQL does. I also thought that "new" > >would > >have been a better choice, but they didn't consult me. :-) > > > >-- > >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-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
This is a baby implementation of the master file merge from the early part of the last century (after the stone knives but somewhat before bearskins). Take two tables, one mounted on tape drive A, with output to tape drive B, updated from a transaction file on tape drive C. Start Friday night. Come Monday morning your master is now on drive B and up-to-date. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp >Sent: Wednesday, 9 May, 2018 03:48 >To: SQLite mailing list >Subject: Re: [sqlite] 3.24 draft - upsert > >On 5/9/18, Olivier Mascia wrote: >> About: >> >> "Column names in the expressions of a DO UPDATE refer to the >original >> unchanged value of the column, before the attempted INSERT. To use >the value >> that would have been inserted had the constraint not failed, add >the special >> "excluded." table qualifier to the column name." >> >> Why using 'excluded' wording for this? > >Because that is what PostgreSQL does. I also thought that "new" >would >have been a better choice, but they didn't consult me. :-) > >-- >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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
Why does SQLite have to follow what PostgreSQL does? I thought SQLite is the leader. Roman Sent from my T-Mobile 4G LTE Device Original message From: Richard Hipp Date: 5/9/18 5:48 AM (GMT-05:00) To: SQLite mailing list Subject: Re: [sqlite] 3.24 draft - upsert On 5/9/18, Olivier Mascia wrote: > About: > > "Column names in the expressions of a DO UPDATE refer to the original > unchanged value of the column, before the attempted INSERT. To use the value > that would have been inserted had the constraint not failed, add the special > "excluded." table qualifier to the column name." > > Why using 'excluded' wording for this? Because that is what PostgreSQL does. I also thought that "new" would have been a better choice, but they didn't consult me. :-) -- 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
Fuck you suck On Wed, 9 May 2018, 09:00 Olivier Mascia, wrote: > About: > > "Column names in the expressions of a DO UPDATE refer to the original > unchanged value of the column, before the attempted INSERT. To use the > value that would have been inserted had the constraint not failed, add the > special "excluded." table qualifier to the column name." > > Why using 'excluded' wording for this? > Couldn't 'new' be used as qualifier instead, akin to trigger syntax? > Might be more coherent, and shorter for the parser. > > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
> Le 9 mai 2018 à 11:48, Richard Hipp a écrit :
>
>> "Column names in the expressions of a DO UPDATE refer to the original
>> unchanged value of the column, before the attempted INSERT. To use the value
>> that would have been inserted had the constraint not failed, add the special
>> "excluded." table qualifier to the column name."
>>
>> Why using 'excluded' wording for this?
>
> Because that is what PostgreSQL does. I also thought that "new" would
> have been a better choice, but they didn't consult me. :-)
They should have. :-)
Considering a simple case like this one, but with a possibly significant number
of columns:
insert into T(K1,K2,C3,C4,...,CN) values (?1,?2,?3,?4,...?N)
on conflict (K1,K2) do update set (C3,C4,...,CN) = (?3,?4,...,?N)
would this:
insert into T(K1,K2,C3,C4,...,CN) values (?1,?2,?3,?4,...?N)
on conflict (K1,K2) do update set (C3,C4,...,CN) =
(excluded.C3,excluded.C4,...,excluded.CN)
have a minor execution advantage or disadvantage over the first form?
If the first form, re-using parameters, is equally efficient (or better), I
will have next to no use of the 'excluded' syntax from our C++ helper around
SQLite API where we code such a simple upsert as:
st.upsert("T", { "K1", "K2" }, { "C3", "C4", ..., "CN" }); // prepare
st.run("ABC", "DEF", 10, 20.0, ..., "TEXT");// bind + exec
st.run("DEF", "GHI", 11, 18.0, ..., "OTHER"); // new bind +
new exec
/* Pre 3.24 implementation used two hidden prepared statement behind (one
update and one insert, running the insert only if the update said 'no
changes'). */
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
On 2018/05/09 11:48 AM, Richard Hipp wrote: On 5/9/18, Olivier Mascia wrote: About: "Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name." Why using 'excluded' wording for this? Because that is what PostgreSQL does. I also thought that "new" would have been a better choice, but they didn't consult me. :-) It is probable they considered confusion with precisely the Trigger identifier we praise for its similarity. On first read, "excluded" seemed unintuitive for me too, but the moment I started thinking in terms of "these are the rows that won't be included in the insert" (i.e. "The Excluded"), it quickly built a neural pathway in the noggin and now seems completely normal. Just needed a moment of acclimatization, as with any New thing. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 draft - upsert
On 5/9/18, Olivier Mascia wrote: > About: > > "Column names in the expressions of a DO UPDATE refer to the original > unchanged value of the column, before the attempted INSERT. To use the value > that would have been inserted had the constraint not failed, add the special > "excluded." table qualifier to the column name." > > Why using 'excluded' wording for this? Because that is what PostgreSQL does. I also thought that "new" would have been a better choice, but they didn't consult me. :-) -- 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] 3.24 draft - upsert
About: "Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name." Why using 'excluded' wording for this? Couldn't 'new' be used as qualifier instead, akin to trigger syntax? Might be more coherent, and shorter for the parser. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
