On 2020/01/08 1:10 PM, Simon Slavin wrote:
I advise you avoid the idea of UPSERT when dealing with SQLite (or better
still, all SQL). It is rarely implemented as a single operation, and you can
get unexpected results with triggers and foreign key children.
I advise you to avoid the idea of driving a Bus to work (or better
still, at all). It is rarely easy to navigate narrow lanes and you can
get unexpected results when trying to park it at the mall.
Rather use a bicycle.
If however your objective is to take 100 other people to work, you'll
find the bus is a godsend - much like UPSERT.
<tongue-removed-from-cheek>
Simon is quite correct, using a function without proper understanding,
results can be somewhat unexpected. Even with proper understanding of
the SQL, it can still be unexpected, considering that different engines
implement it differently. A shining example of this is "REPLACE" -
almost everyone who posted about it here misunderstood what it really
does[1], often causing the exact problems Simon warns about.
That said, I can't agree with advising against UPSERT.
There is no unknown magic going on with UPSERT (or any other fully
supported SQL feature in SQLite), if you do need it, do read up properly
on it (specific to the platform you will use it on) and it will work as
advertised. (And in the rare case it doesn't, a bug report here usually
remedies that in about a day).
Cheers,
Ryan
[1] - They often use REPLACE having come from a MySQL background, and
then they often learn that even in MySQL it doesn't work as they had
imagined.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
