MySQL update field based on conditional sum from other table

Question

I am trying to populate an attendance system with values from a legacy system. I am currently tracking all_time attendance (which is simple), attendance in this year, and attendance since last grading (examination). To avoid potential contamination, I am storing legacy data, and want to import it into my totals a bit at a time.

This is my basic data model:

-- information about the attendee
CREATE TABLE core_member (
    id INT UNSIGNED AUTO_INCREMENT, 
    name VARCHAR (250), 
    dob DATE,
    last_grade_date date default '2014-12-14',
    grade INT UNSIGNED NOT NULL DEFAULT 50,
    PRIMARY KEY (id)
);

-- attendee data 
INSERT INTO core_member values(1, 'Donald Duck', '1950-03-01', '2015-06-15', 10);
INSERT INTO core_member values(2, 'Goofy', '1950-04-17', '2014-06-15', 42);


-- attendance sums (1:1 with antendee)
CREATE TABLE core_attendance(
    medlemsid INT UNSIGNED,
    imported INT DEFAULT 0,
    all_time INT DEFAULT 0,
    since_last_grad INT DEFAULT 0,
    UNIQUE KEY medlemsid (medlemsid),
    FOREIGN KEY (medlemsid) REFERENCES core_member (id)
);

-- attendance sum data
INSERT INTO core_attendance values(1,100,150,0);
INSERT INTO core_attendance values(2,80,103,3);

-- historic attendance
CREATE TABLE core_historic_attendance (
  medlemsid int(10) unsigned NOT NULL,
  year int(11) NOT NULL,
  month enum('none','januar','februar','marts','april','maj','juni','juli','august','september','oktober','november','december') NOT NULL DEFAULT 'none',
  attendance int(11) DEFAULT '0',
  UNIQUE KEY unq (medlemsid,year,month),
  KEY medlemsid_idx (medlemsid),
  CONSTRAINT medlemsid FOREIGN KEY (medlemsid) REFERENCES core_member (id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

-- historic attendance data
INSERT INTO core_historic_attendance values(1,2011,'none',54);
INSERT INTO core_historic_attendance values(1,2012,'none',43);
INSERT INTO core_historic_attendance values(1,2013,'none',61);
INSERT INTO core_historic_attendance values(1,2014,'none',49);
INSERT INTO core_historic_attendance values(1,2015,'januar',19);
INSERT INTO core_historic_attendance values(1,2015,'februar',14);
INSERT INTO core_historic_attendance values(1,2015,'marts',17);
INSERT INTO core_historic_attendance values(2,2011,'none',54);
INSERT INTO core_historic_attendance values(2,2012,'none',43);
INSERT INTO core_historic_attendance values(2,2013,'none',61);
INSERT INTO core_historic_attendance values(2,2014,'none',49);
INSERT INTO core_historic_attendance values(2,2015,'januar',19);
INSERT INTO core_historic_attendance values(2,2015,'februar',4);
INSERT INTO core_historic_attendance values(2,2015,'marts',7);

I need to add the sum of all the values from core_historic attendance where the year is greater than the year of the last grading to the since_last_grad column in the core_member table, for the member in question (initial load).

I gather from this post ( mysql update column with value from another table ), that I have to do something like

UPDATE core_members INNER JOIN core_historic_attendance ON id = core_historic_attendance.medlemsid having`year` > year(last_grad_date)
SET since_last_grad = since_last_grad +  SUM(attendance);

But I'm a bit stuck, since i want only the sum of the core_historic_attendance records where the year is greater than year(last_grad_date)

EDIT:

I have corrected the SQL as suggested by Sasha, nut I get an error code 1111 "Invalid use of group function"

I have created an SQL Fiddle as well

Sasha Pachev · Accepted Answer · 2015-11-19T03:13:16.863

1

Does this work for you:

CREATE TEMPORARY TABLE t1 (key(medlemsid)) SELECT core_historic_attendance.medlemsid,SUM(attendance) as total_attendance
FROM core_member INNER JOIN core_historic_attendance ON
 id = core_historic_attendance.medlemsid AND `year` > year(last_grade_date)
GROUP BY medlemsid;

UPDATE core_attendance INNER JOIN t1  USING(medlemsid)
SET since_last_grad = since_last_grad +  t1.total_attendance;

?

edited Nov 19 '15 at 03:13

answered Nov 19 '15 at 00:35

Sasha Pachev

5,162
3
20
20

unfortunately no.I get an error code 1111 "Invalid use of group function" and I have to write it as `UPDATE core_member INNER JOIN core_historic_attendance ON id = core_historic_attendance.medlemsid AND year > year(last_grad_date) SET since_last_grad = since_last_grad + SUM(attendance)` – JoSSte Nov 19 '15 at 01:36
Sorry about that, that is what I got for shooting off the hip. Check my updated answer, I actually tested it. – Sasha Pachev Nov 19 '15 at 03:15

MySQL update field based on conditional sum from other table

1 Answers1