3e8.org

I use SQLite3 for my searches database and I was looking for a way to create a frequency table of search terms--either updating the term count or inserting new termsas needed. Evidently, according to this blog post, MySQL has an extension INSERT ... ON DUPLICATE KEY UPDATE which lets you insert or update as needed. The solution in that post for SQLite3 is programmatic: check if an INSERT fails, and do an UPDATE if so.

However, I thought this was not particularly elegant and it also would not work from a trigger. Here is the autovivification solution I came up with:

CREATE TABLE terms(count INTEGER, term TEXT PRIMARY KEY);
...
INSERT OR IGNORE INTO terms(count, term) VALUES (0, 'my term');
UPDATE terms SET count = count + 1 WHERE term = 'my term';

Execute these two statements whenever you want to bump a term's frequency. The ON CONFLICT IGNORE (aka OR IGNORE) causes the INSERT to silently fail on constraint violation; specifically, the uniqueness of the primary key. The base value of 0 ensures the count starts at 1.