Monday, May 16, 2011

sqlite: counter table idiom

Being in process of building my idioms “collection” for sqlite/iphone, wanted to share/log few lines showing “upsert” and coalesce usage.

create table usage (pid string, channel integer, count integer, PRIMARY KEY (pid, channel));
 
INSERT OR REPLACE INTO usage (pid, channel, count) 
    values (11, 1, coalesce((select count+1 from usage where pid = 11 and channel = 1),1));

The above sample counts number of usages for the selected pid and channel. Provider id and order submission channel in my case.

Post a Comment