On Friday, 22 February, 2019 09:08, Constantine Yannakopoulos <alfasud...@gmail.com>:
> I would like to find whether an upsert operation actually did an > insert or an update, preferably without having to execute extra > SQL statements before or after it. I doubt it very much. You see, an UPSERT statement (or rather an INSERT with one or more ON CONFLICT clauses) is an atomic statement. If no error (ABORT/FAIL) is thrown, the statement executed successfully and did whatever it was that the statement was "a declaration of doing". If you require to know what the various "component parts" of the declared statement are doing, then you need to execute in smaller parts. > I thought of using last_insert_rowid() before and after the > upsert and check if the result has changed but while this > seems to work for normal tables It only seems to work but is not reliable (that is, it can be proven/demonstrated that there is at least one case in which reliance on this method will lead to an incorrect assumption and therefore the method is unreliable). > it will not work for WITHOUT ROWID tables. Is there another > way that works consistently both with tables with or without rowid? Yes. That is to "unbundle" the UPSERT into its component parts and execute them in a single transaction (which is what the combined UPSERT does) tracking the progress along the way. If you already have an outer transaction in progress then SAVEPOINTs might be in order. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
