On Wed, Feb 27, 2013 at 7:53 AM, James K. Lowden <jklow...@schemamania.org> wrote: > begin transaction; > update tgt > set name = (select name from src where tgt.key = src.key) > where exists ( > select 1 from src > where src.key = tgt.key > ); > -- check for error
Yes, this is what I'd call the canonical way to implement the missing OR IGNORE (both in SQLite3 apps and in general), and combined with the subsequent INSERT this is the canonical way to implement INSERT OR UPDATE. > insert into tgt (name) > select name from src > where not exists ( > select 1 from tgt > where tgt.key = src.key > ); > -- check for error > commit transaction; > > It's only close, not equivalent, because MERGE is atomic: here src and > tgt could change between UPDATE and INSERT. That you'll have to deal > with using timestamps or some form of advisory locking. Who might change it? We're in an exclusive transaction at this point and SQLite3 allows only one writer at a time. Another thread with the same db handle? Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
