sql - Update multiple columns on a row with a single select in sqlite -

in sqlite need update row counts of related table.

the query below want walks table multiple times counts:

update overallcounts set   total = (count(*) widgets joinid=1234),   totalc = (count(*) widgets joinid=1234 , source=0),   totall = (count(*) widgets joinid=1234 , source=2),   iic = (select case when count(*)>0 1 else 0 end widgets joinid=1234 , widgets.source=0),   il = (select case when count(*)>0 1 else 0 end widgets joinid=1234 , widgets.source=2) id=1234 

this query retrieves want need turn output update statement:

select   count(*) total,   sum(case when source=0 1 else 0 end) totalc,   sum(case when source=2 1 else 0 end) totall,   case when source=0 1 else 0 end iic,   case when source=2 1 else 0 end il widgets joinid=1234 

sqlite not support joins in update queries. limitation of sqlite design. however, can still in sqlite using powerful insert or replace syntax. disadvantage of have entry in overallcounts (if did not have entry inserted). syntax be:

insert or replace overallcounts (total, totalc, totall, iic, il) select   count(*) total,   sum(case when source=0 1 else 0 end) totalc,   sum(case when source=2 1 else 0 end) totall,   case when source=0 1 else 0 end iic,   case when source=2 1 else 0 end il widgets joinid=1234 on conflict replace