Update multiple columns on a row with a single sel

Question

In SQLite I need to update row counts of a related table.

The query below does what I want but it walks the table multiple times to get the counts:

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(*)>0 THEN 1 ELSE 0 END FROM widgets WHERE joinId=1234 AND widgets.source=0),
  il = (SELECT CASE WHEN COUNT(*)>0 THEN 1 ELSE 0 END FROM widgets WHERE joinId=1234 AND widgets.source=2)
WHERE id=1234

This query retrieves exactly what I want quickly but I need to turn its output into an update statement:

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

Answer 1

In the given statement, both ItemName and ItemCategoryName are updated in a single statement with UPDATE. It worked in my SQLite.

UPDATE Item SET ItemName='Tea powder', ItemCategoryName='Food' WHERE ItemId='1';

Answer 2

@cha why not check if exists?

INSERT OR REPLACE INTO overallCounts (total, totalC, totalL, iic, il)
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
AND EXISTS (SELECT joinId FROM overallCounts WHERE joinId=1234)
ON CONFLICT REPLACE

Answer 3

UPDATE overallCounts SET (total, totalC, totalL, iic, il) =
  (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)
WHERE joinId=1234;

Answer 4

SQLite does not support JOINs in UPDATE queries. It is a limitation of SQLIte by design. However, you can still do it in SQLite using its powerful INSERT OR REPLACE syntax. The only disadvantage of this is that you will always have an entry in your overallCounts (if you did not have an entry it will be inserted). The syntax will be:

INSERT OR REPLACE INTO overallCounts (total, totalC, totalL, iic, il)
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
ON CONFLICT REPLACE