You can always do insert into table (id, count) values (?1, (select count(*) + 1 from table where id = ?1))
Though I'd be cautious about race condition that seems like possible here when after select returned some value and before insert was made another process made another insert with the same id. Looks like you have to prepend it with BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Pavel On Tue, Aug 18, 2009 at 1:06 PM, andrew fabbro<[email protected]> 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. > > I could do this: > > 1. try insert > 2. if SQLITE_CONSTRAINT, fetch the value of count > 3. increment count > 4. issue an UPDATE > > This seems wasteful...is there an efficient way to do this, say as a > trigger? > > Thanks! > _______________________________________________ > 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
