SQLite: update statements

--
-- Compare with ../replace/replace_with_aggregate_function.sql
--
-- See also http://stackoverflow.com/questions/34960972
--
drop table if exists tq84_child;
drop table if exists tq84_parent;

create table tq84_parent (
  id                text primary key,
  avg_val           float,
  min_val           float,
  max_val           float
);

create table tq84_child (
  id_parent         text references tq84_parent,
  val               int
);


insert into tq84_parent (id) values('A');
insert into tq84_parent (id) values('B');

insert into tq84_child values ('A',  1);
insert into tq84_child values ('A',  2);
insert into tq84_child values ('A',  3);

insert into tq84_child values ('B',  6);
insert into tq84_child values ('B',  7);
insert into tq84_child values ('B',  8);
insert into tq84_child values ('B',  9);
insert into tq84_child values ('B', 10);

update
  tq84_parent
set
 (
  avg_val,
  min_val,
  max_val
 )
= (select
     avg(val),
     min(val),
     max(val)
   from
     tq84_child
   where
     id_parent = id
);

.mode columns

select * from tq84_parent;
--
--  id  avg_val  min_val  max_val
--  --  -------  -------  -------
--  A   2.0      1.0      3.0
--  B   8.0      6.0      10.0

Apparently, SQLite follows the PostgreSQL SQL dialect (see here), so that in the following example, the table name of the table being updated does not have to be mentioned explicitly in the FROM clause:

create table T (
   id  text   primary key,
   val number
);

create table U (
   id_t  text references T,
   diff  number
);

insert into T values ('A',  100);
insert into T values ('B',  200);

insert into U values ('A',  2);
insert into U values ('A',  3);
insert into U values ('A',  4);

insert into U values ('B',  5);
insert into U values ('B',  6);

update T
set
   val = val - total.diff
from
   (select
       id_t,
       sum(diff) as diff
    from
       U
    group by
       id_t
    )       as total
where
   T.id = total.id_t;

.mode columns
select * from T;
--
--  id  val
--  --  ---
--  A   91
--  B   189