> Le 9 mai 2018 à 11:48, Richard Hipp <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
