Does REPLACE do what you want? http://www.sqlite.org/lang_replace.html
Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: [email protected] [[email protected]] on behalf of Tony Caras [[email protected]] Sent: Thursday, January 12, 2012 6:17 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] UPSERT again UPSERT (really means update the record if it exists otherwise insert the record) AFTER INSERT (I meant a trigger after an insert. In this case I have access to the new value but not the "old" values in the record.) If UPDATE would insert the record if it didn't exist then I could use the trigger you have suggested. ________________________________ From: Simon Slavin <[email protected]> To: Tony Caras <[email protected]>; General Discussion of SQLite Database <[email protected]> Sent: Thursday, January 12, 2012 4:40 PM Subject: Re: [sqlite] UPSERT again On 12 Jan 2012, at 11:20pm, Tony Caras wrote: > The problem is, that if I create AFTER INSERT then I don't know what the > original state of the blocked field was so I don't whether to increment or > decrement or leave the counter alone. I don't understand what you mean by 'UPSERT' or 'create AFTER INSERT'. If you're doing an INSERT then the field doesn't have an original state: it's a new row. If you're doing an UPDATE a TRIGGER does indeed have access to the old value of a field changed by UPDATE: http://www.sqlite.org/lang_createtrigger.html So you can do CREATE TRIGGER ... AFTER UPDATE ... BEGIN UPDATE ... SET totalBlocked = totalBlocked - 1 WHERE old.blocked; UPDATE ... SET totalBlocked = totalBlocked + 1 WHERE new.blocked; END; Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
