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

发布时间：2020-12-12 18:59:51 所属栏目：百科来源：网络整理

导读：在SQLite中,我需要更新相关表的行计数. 下面的查询执行我想要的但是它多次遍历表以获取计数： UPDATE overallCounts SET total = (count(*) FROM widgets WHERE joinId=1234),totalC = (count(*) FROM widgets WHERE joinId=1234 AND source=0),totalL = (co

在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 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!