Updating multiple columns with one subquery

Question

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?

Is this your actual tq84_parent table, or are there more columns? — CL.
– CL., Commented Jan 23, 2016 at 7:55
There are more columns in tq84_parent. If it makes a difference, I'd still be interested for a solution where it were the actual tq84_parent table. — René Nyffenegger
– René Nyffenegger, Commented Jan 23, 2016 at 7:59

Joachim Isaksson · Accepted Answer · 2016-01-23 09:36:22Z

1

Since id and id_parent are primary and foreign keys it means id is unique and id_parent always references an existing id.

That means you can use REPLACE to change the table since the unique key is always violated (aka no inserts will be done), and REPLACE can take a subquery and set multiple columns at once;

REPLACE INTO tq84_parent (id, avg_val, min_val, max_val)
  SELECT id_parent, AVG(val), MIN(val), MAX(val)
  FROM tq84_child
  GROUP BY id_parent;

An SQLfiddle to test with.

Note: As CL points out, fields that are not set in the query will have their default values since the rows are replaced, not updated. This means that if you have other fields in the table that you need to keep unchanged, your old method is still the way to do it.

edited Jan 23, 2016 at 9:36

answered Jan 23, 2016 at 8:18

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

2 Comments

CL. Over a year ago

REPLACE never updates the table, it just deletes the old row.

Joachim Isaksson Over a year ago

@CL. You are correct of course, that's what I get for answering database questions before first coffee. Thanks.

CL. · Accepted Answer · 2016-01-23 09:03:48Z

0

The actual speed of the query execution depends mostly of the amount of disk I/O that needs to be done.

With the database schema shown above, each subquery needs to scan the entire table. If you have a covering index on all the columns needed to compute the aggregate values, each subquery needs to scan only a small part of the index, so in practice, the data is guaranteed to already be in the cache when doing multiple subqueries for the same ID:

CREATE INDEX xxx ON tq84_child(id_parent, val);

answered Jan 23, 2016 at 9:03

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

Updating multiple columns with one subquery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related