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

Question

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

cha · Accepted Answer · 2013-04-18 02:06:42Z

7

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

answered Apr 18, 2013 at 2:06

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sinchi Over a year ago

Would this method overwrite any additional fields in the record with joinId=1234?

lrcrb Over a year ago

this definitely removed the row with the primary key conflict and then inserts only the values you provide, thus overwriting the other fields.

Michael Petrotta · Accepted Answer · 2013-07-07 08:05:29Z

6

In the given statement, both ItemName and ItemCategoryName are updated in a single statement with UPDATE. It worked in my SQLite.

UPDATE Item SET ItemName='Tea powder', ItemCategoryName='Food' WHERE ItemId='1';

edited Jul 7, 2013 at 8:05

Michael Petrotta

61.1k27 gold badges153 silver badges181 bronze badges

answered Jul 7, 2013 at 7:44

Bijumon V G

1091 silver badge2 bronze badges

1 Comment

C Perkins Over a year ago

How did this get so many upvotes? The question specifically says "with a single select", but this answer has NO SELECT clause, just literal values.

Vladimir Udintsev · Accepted Answer · 2019-01-23 09:14:47Z

6

UPDATE overallCounts SET (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)
WHERE joinId=1234;

answered Jan 23, 2019 at 9:14

Vladimir Udintsev

611 silver badge1 bronze badge

1 Comment

Benny Jobigan Over a year ago

this is valid since SQLite version 3.15.0 (2016-10-14), when a list of column names can be updated with a query row with the same size.

Then Enok · Accepted Answer · 2016-12-09 00:52:09Z

0

@cha why not check if exists?

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
AND EXISTS (SELECT joinId FROM overallCounts WHERE joinId=1234)
ON CONFLICT REPLACE

answered Dec 9, 2016 at 0:52

Then Enok

6616 silver badges18 bronze badges

Comments

Pelton · Accepted Answer · 2023-05-10 18:37:32Z

I know this answer comes VERY late. Here's a TRICK for complex updates: use a trigger on a dummy temporary table which never receives any records.

Then formulate your query, however complex, containing joins, aggregates, withs ... .... Instead of updating real_table, INSERT into DUMMY. Here is the code example:

drop table if exists dummy;
create temporary table dummy (id number, val1, val2);

create trigger dummy_ins before insert on dummy
begin
   update real_table set val1 = new.val1, val2 = new.val2 where id = new.id;
   select raise(ignore);
end;

--and here's how the update is performed:
insert into dummy
select id, val1, val2
from <whatever complex query you may formulate>}

You will notice that this trick is extremely fast, much faster than update with a where clause ... Additionally, everything contained within the trigger will also consider updates performed recently, by the code itself (unlike a global update which first prepares the set to update and then executes, without considering the results of its proper update.

HINT: For debugging reasons, you may comment out the "--select raise(ignore)" line. Like that, you can trace all those updates done

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related