On Thu, Apr 10, 2008 at 09:02:39PM +0200, Petite Abeille scratched on the wall: > Hello, > > How does one emulate a DML MERGE statement in SQLite [1]? > > INSERT OR REPLACE sounds promising but the REPLACE documentation under > the ON CONFLICT clause seems to imply that in the case of a constraint > violation the existing row will be deleted entirely and then replaced > by a brand new row instead of being merely updated [2].
Yes, that is true. For better or worse, it is an "INSERT OR REPLACE" not an "INSERT OR UPDATE". It is just an INSERT statement with an extra bit of protection that will delete a confilicting row if it gets in the way of the insert. It is an INSERT either way, however, and that means a new ROWID and new default/NULL values for any columns not given values in the INSERT. Values are not copied from the (possibly) deleted row unless you go out of your way to do so. Using INSERT OR REPLACE to updating tables that have data such as (<event>, <time-last-seen>) is really straight forward. It can also be used to update tables in the (<event>, <count>) style-- the syntax is just a bit more involved. There was a pretty good thread about this second situation about two months ago. If that's the type of thing you're looking to do, have a look a this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg31705.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
