> insert or replace into mytable(id, count) > values (:id, coalesce((select count from mytable where id=:id), 0) + 1);
I guess it doesn't work as OP intended: sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1), 0) + 1); sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 1|2 But this does: sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1 order by count desc), 0) + 1); sqlite> select * from t; id|count 1|1 1|2 1|3 1|4 1|5 sqlite> delete from t; sqlite> insert or replace into t(id, count) values (1, coalesce((select count from t where id=1 order by count desc), 0) + 1); sqlite> select * from t; id|count 1|1 Pavel On Wed, Aug 19, 2009 at 12:14 AM, Igor Tandetnik<[email protected]> wrote: > andrew fabbro wrote: >> What is the most efficient way to code either a trigger or the app so >> that it increments a "count" field if the "id" field exists? >> >> For example, say you have a table with fields of "id" and "count". >> >> First row is an id of 3, so "count" is set to 1. >> Next row is an id of 4, so "count" is set to 1. >> Third row is an id of 3 again, so "count" is incremented to 2. > > insert or replace into mytable(id, count) > values (:id, coalesce((select count from mytable where id=:id), 0) + 1); > > Igor Tandetnik > > > > _______________________________________________ > 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
