At 03:47am on 2009 March 08, VF did write: > CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping); > CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key); > > I am trying to do an upsert with the following logic: > > UPDATE MAPPINGS_$idx > SET counter = counter + 1 > , timeModified = CURRENT_TIMESTAMP > WHERE > key = ? AND > mapping = ?; > IF rowcount == 0 -- here tried rowid and other variations, too, it still > fails at he IF > BEGIN > INSERT INTO MAPPINGS_$idx > ( key , mapping , rank , counter , timeCreated , timeModified) > values ( ? , ? , 1 , 1 , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) > END; > Unfortunately, it fails. I ended up having separate update, check the number > of rows modified, and if 0 - do insert in a separate statement. It works, > but painfully slow. Because of this I can't do BEGIN/COMMIT transaction from > the SQL.
Is it possible for you to do the following? The INSERT should fail silently if you violate a uniqueness constraint; we also set the initial counter to 0 as it will be immediately bumped to 1 by the UPDATE. You should be able to wrap the whole thing in a transaction. *** INSERT OR IGNORE INTO MAPPINGS_$idx (key, mapping, rank, counter, timeCreated, timeModified) values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); UPDATE MAPPINGS_$idx SET counter = counter + 1, timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?; -- [email protected] / 0x43340710 / 517B C658 D2CB 260D 3E1F 5ED1 6DB3 FBB9 4334 0710 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
