使用sqlite中的单个select更新行上的多个列

概述在SQLite中,我需要更新相关表的行计数. 下面的查询执行我想要的但是它多次遍历表以获取计数： UPDATE overallCounts SET total = (count(*) FROM widgets WHERE joinId=1234), totalC = (count(*) FROM widgets WHERE joinId=1234 AND source=0), tota 在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 ilFROM WidgetsWHERE joinID=1234

解决方法 sqlite不支持UPDATE查询中的JOIN.这是sqlIte的设计限制.
但是,您仍然可以使用其强大的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 ilFROM WidgetsWHERE joinID=1234ON CONFliCT REPLACE

总结

以上是内存溢出为你收集整理的使用sqlite中的单个select更新行上的多个列全部内容，希望文章能够帮你解决使用sqlite中的单个select更新行上的多个列所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。