SQLite不支持UPDATE查询中的JOIN。这是SQLIte在设计上的局限性。但是，您仍然可以在SQLite中使用其强大的INSERT OR REPLACE语法来执行此操作。这样做的唯一缺点是你将始终在overallCounts中有一个条目（如果你没有条目，它将被插入）。语法为：

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

在给定的语句中，ItemName和ItemCategoryName都在一个带有UPDATE的语句中更新。它在我的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;

@cha为什么不检查是否存在？

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

我知道这个答案来得很晚。这里有一个复杂更新的技巧：在从不接收任何记录的虚拟临时表上使用触发器。
然后制定您的查询，无论多么复杂，包含连接，聚合，与.......而不是更新真实的_table，INSERT到DUMMY。下面是代码示例：

drop table if exists dummy;
create temporary table dummy (id number, val1, val2);

create trigger dummy_ins before insert on dummy
begin
   update real_table set val1 = new.val1, val2 = new.val2 where id = new.id;
   select raise(ignore);
end;

--and here's how the update is performed:
insert into dummy
select id, val1, val2
from <whatever complex query you may formulate>}

你会注意到这个技巧非常快，比用where子句更新要快得多……此外，触发器中包含的所有内容还将考虑最近由代码本身执行的更新（与全局更新不同，全局更新首先准备要更新的集合，然后执行，而不考虑其正确更新的结果。
提示：出于调试的原因，您可以注解掉“--select raise（ignore）”行。就像这样，你可以追踪所有的更新完成