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

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 ilFROM widgetsWHERE joinId=1234ON CONFLICT REPLACE

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';

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;