在sqlite中,我需要更新相关表的行计数.
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
这个查询准确地检索了我想要的内容,但我需要将其输出转换为更新语句：
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
解决方法
sqlite不支持UPDATE查询中的JOIN.这是sqlIte的设计限制.
但是,您仍然可以使用其强大的INSERT OR REPLACE语法在sqlite中执行此操作.这样做的唯一缺点是你总是在你的overallCounts中有一个条目(如果你没有一个条目,它将被插入).语法将是：
但是,您仍然可以使用其强大的INSERT OR REPLACE语法在sqlite中执行此操作.这样做的唯一缺点是你总是在你的overallCounts中有一个条目(如果你没有一个条目,它将被插入).语法将是：
INSERT OR REPLACE INTO overallCounts (total,totalC,totalL,iic,il) SELECT count(*) as total,case when source=2 then 1 else 0 end as il FROM widgets WHERE joinId=1234 ON CONFLICT REPLACE
