Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple columns on a row with a single select in sqlite

Tags:

sql

sqlite

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
like image 723
Sinchi Avatar asked Apr 17 '13 22:04

Sinchi


People also ask

How do I update multiple columns in SQLite?

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.

Can we change multiple columns using single update statement?

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.

How do I update multiple fields?

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.


1 Answers

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
like image 178
cha Avatar answered Sep 27 '22 23:09

cha