On 2016/06/30 2:48 PM, Olivier Mascia wrote:
Le 30 juin 2016 à 13:34, R Smith <rsm...@rsweb.co.za> 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. :)
Ha, ok, but I wasn't really complaining about the way it is done in MSSQL, I was merely saying what is needed for it to work, and I see now you mean more of a INSERT OR UPDATE the way MySQL does it. Don't disregard the above MERGE statement though, it is a very powerful piece of SQL and I quite like to use it. Very often (if not mostly) you want to update and/or insert only in certain cases (WHERE modified = 1) - taking values from a table (rather than just VALUES like I did in my examples) and for that this MERGE mechanism is an amazing device. It's basically UPSERT with expressions, filtering and sub-queries allowed.
If we /are/ going to go full regalia in SQLite, I'd push for MERGE[1] actually rather than just INSERT - UPDATE, but the caveats I mentioned earlier remain.
[1] Perhaps borrowing from the PostGres MERGE implementation rather... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
