vendredi 22 janvier 2016

Updating multiple columns with one subquery

I have a classical parent - child relationship in sqlite:

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

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

which is filled as follows

insert into tq84_parent (id) values(1);
insert into tq84_parent (id) values(2);

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

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

Now, I'd like to update tq84_parent that its columns avg_val, min_val and max_val reflect the according aggregate values of its child table.

I can achieve this with this statement:

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

The result, as desired is:

.mode columns

select * from tq84_parent;

1           2.0         1.0         3.0
2           8.0         6.0         10.0

I fear, however, that the performance of such a statement is not optimal as the number of records grows in both tq84_parent and tq84_child because almost the same query is made three times instead of once.

So, I'd like to go with something like

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

which doesn't work.

So, is there a way to rewrite my query?

Aucun commentaire:

Enregistrer un commentaire