On Thu, Jun 30, 2016 at 7:48 AM, Olivier Mascia wrote: > > Le 30 juin 2016 à 13:34, R Smith a écrit : > > > > MERGE dbo.xxx AS T > > USING dbo.yyy AS S > > ON T.SomeID = S.SomeID > > WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever > checking is relevant > > THEN UPDATE SET T.ValueThatNeedsChanging = NewValue > > WHEN NOT MATCHED > > THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns) > > VALUES () / SELECT clauses > > ; > > I, hopefully, never used such a piece of coding in my whole life and I > know, now, why all my human body cells refrained me to ever even approach > MSSQL. :) > > The - useful - "upsert" which I can make good use of is the simpler one > you can find in FirebirdSQL, where it is called UPDATE OR INSERT with > pretty much the same syntax as an insert. > > Easy to read, useful and effective for what use cases it is designed for. > > I've used MERGE INTO a lot and sometimes wished that SQLite had it, but i can understand why it's not a priority. To me, the real value of MERGE is the atomicity of the operation. You can perform inserts, updates, and deletes all in one statement--No chance for race conditions if data changes between the operations.
In SQLite where only a single writer is allowed at a time and the only isolation level available Serializable, all you need to do is BEGIN IMMEDIATE and you get the atomicity you need, even if it seems unnatural (to me) to use two or three different statements to do what is logically one operation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
