In SQLite I need to update row counts of a related table.
The query below does what I want but it walks the table multiple times to get the counts:
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
This query retrieves exactly what I want quickly but I need to turn its output into an update statement:
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
First, specify the table where you want to update after the UPDATE clause. Second, set new value for each column of the table in the SET clause. Third, specify rows to update using a condition in the WHERE clause. The WHERE clause is optional.
The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
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 il
FROM widgets
WHERE joinId=1234
ON CONFLICT REPLACE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With