title: Update multiple columns on a row with a single select in sqlite
body: <p>In SQLite I need to update row counts of a related table.</p>
  
  <p>The query below does what I want but it walks the table multiple times to get the counts:</p>
  
  <pre><code>UPDATE overallCounts SET
    total = (count(*) FROM widgets WHERE joinId=1234),
    totalC = (count(*) FROM widgets WHERE joinId=1234 AND source=0),
    totalL = (count(*) FROM widgets WHERE joinId=1234 AND source=2),
    iic = (SELECT CASE WHEN COUNT(*)&gt;0 THEN 1 ELSE 0 END FROM widgets WHERE joinId=1234 AND widgets.source=0),
    il = (SELECT CASE WHEN COUNT(*)&gt;0 THEN 1 ELSE 0 END FROM widgets WHERE joinId=1234 AND widgets.source=2)
  WHERE id=1234
  </code></pre>
  
  <p>This query retrieves exactly what I want quickly but I need to turn its output into an update statement:</p>
  
  <pre><code>SELECT
    count(*) as total,
    sum(case when source=0 then 1 else 0 end) as totalC,
    sum(case when source=2 then 1 else 0 end) as totalL,
    case when source=0 then 1 else 0 end as iic,
    case when source=2 then 1 else 0 end as il
  FROM widgets
  WHERE joinId=1234
  </code></pre>