CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 3095

MYSQL - Updating count in one tabled based on two other tables joined

I have 3 related tables. Adults, Children and AC. Adults contains an INT column to count high school seniors. Children contains a column with year of highs school graduation. AC links the adult.id to the children.id.

CREATE TABLE adults (
  id INT,
  name VARCHAR(10),
  seniors INT DEFAULT 0
) ;

INSERT INTO adults (id, name) VALUES
(1, 'adam'),
(2, 'bob');

CREATE TABLE children (
  id INT,
  name VARCHAR(10),
  grad VARCHAR(4)
) ;

INSERT INTO children (id, name, grad) VALUES
(1, 'sally', '2016'),
(2, 'johnny', '2017'),
(3, 'eric', '2016'),
(4, 'billy', '2016'),
(5, 'rachel', '2016');

CREATE TABLE pc (
  id INT,
  a_id INT,
  c_id INT
) ;

INSERT INTO pc (id, a_id, c_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 3),
(5, 2, 2);

SQLFiddle: http://sqlfiddle.com/#!2/89281e

So I want to update adults.seniors to the count of '2016' children they're linked to. So adult #1 would be "2" (sally and eric), and adult #2 "1" (eric).

The real data will be run across 25,000+ children being matched up to 40,000+ parents with a row count on the "pc" table above 3,000,000 rows - so looking for efficiency. I started working down this path but a) it's not working for obvious reasons and b) I doubt it would be efficient...

UPDATE adults a SET
    seniors = (
       SELECT p.a_id, count(*)
       FROM pc p
       INNER JOIN children c ON c.id = p.c_id
       WHERE c.grad = '2016'
       GROUP BY p.c_id)
   WHERE p.a_id = a.id;

I'm thinking there has to be a better way of doing this with joins but can't seem to wrap my head around it.

Upvotes: 0

Views: 86

Answers (1)

James Jithin
James Jithin

Reputation: 10555

You should be looking for this update statement:

UPDATE adults a
        JOIN
    (SELECT 
        p.a_id, COUNT(*) childrencount
    FROM
        pc p
    INNER JOIN children c ON c.id = p.c_id
    WHERE
        c.grad = '2016'
    GROUP BY p.a_id) c ON (a.id = c.a_id)
SET 
    seniors = c.childrencount;

Upvotes: 1

Related Questions