MySQL correlated subquery

Question

Having difficulty articulating this correlated subquery. I have two tables fictitious tables, foo and bar. foo has two fields of foo_id and total_count. bar has two fields, seconds and id.

I need to aggregate the seconds in bar for each individual id and update the total_count in foo. id is a foreign key in bar for foo_id.

I've tried something similar without much luck:

UPDATE foo f1 set total_count = (SELECT SUM(seconds) from bar b1 INNER JOIN foo f2     WHERE b1.foo_id = f2.id) WHERE f1.foo_id = bar.id;

Answer 1

I hope I understood your question right.

You have the following tables:

• table foo - columns: id and total_count
• table bar - culumns: foo_id (references foo.id) and seconds

The following query should work (update all total_count rows in table foo):

UPDATE foo AS f1
SET total_count = (
  SELECT SUM(seconds) 
  FROM bar INNER JOIN foo 
  WHERE foo_id = f1.id
);

I'm not sure what you are trying to do with your last WHERE clause (WHERE f1.foo_id = bar.id;).

Answer 2

UPDATE foo f1
SET total_count = (SELECT SUM(seconds)
FROM bar b1 WHERE b1.id = f1.foo_id)

You should have access to the appropriate foo id within the sub-query, so there is no need to join in the table.

Answer 3

This really opens the door to consistency issues. You might consider creating a view rather than mutating the foo table:

CREATE VIEW foo AS
SELECT id, sum(seconds) from bar group by id;

Answer 4

Just to offer an alternative, I like to use MySQL's nifty multi-table updates feature:

UPDATE foo SET total_count = 0;

UPDATE foo JOIN bar ON (foo.foo_id = bar.id)
  SET foo.total_count = foo.total_count + bar.seconds;

Answer 5

In larger data sets, correlated subqueries can be very resource-intensive. Joining to a derived table containing the appropriate aggregates can be much more efficient:

create table foo ( foo_id int identity, total_count int default 0 )
create table bar ( foo_id int, seconds int )

insert into foo default values
insert into foo default values
insert into foo default values

insert into bar values ( 1, 10 )
insert into bar values ( 1, 11 )
insert into bar values ( 1, 12 )
    /* total for foo_id 1 = 33 */
insert into bar values ( 2, 10 )
insert into bar values ( 2, 11 )
    /* total for foo_id 2 = 21 */
insert into bar values ( 3, 10 )
insert into bar values ( 3, 19 )
    /* total for foo_id 3 = 29 */

select *
from foo

foo_id      total_count
----------- -----------
1           0
2           0
3           0

update  f
set     total_count = sumsec
from    foo f
        inner join (
                     select foo_id
                          , sum(seconds) sumsec
                     from   bar
                     group by foo_id
                   ) a
            on f.foo_id = a.foo_id

select *
from foo

foo_id      total_count
----------- -----------
1           33
2           21
3           29