mcadamsjustin mcadamsjustin - 1 month ago 13
MySQL Question

MYSQL Trigger - Using SUM with JOIN

I have two tables:

A

week
table with columns
week
,
Team_Number
, and
Points
.

A
Teams
table with columns
Team_Number
and
Session1Points
.

I'd like to
sum
all of the
Points
from the
week
table where
week
column is between 1 and 9, then add that to the
Teams
table in the
Session1Points
column.

I've tried:

UPDATE Teams P
SET Session1Points =
(SELECT SUM(Points) from week
where week.Team_Number= P.Team_Number)
where P.Team_Number = New.Team_Number


that's not working. I'd like it to trigger after I update the
week
table. Do I need to
Join
first?

Answer

You can use a JOIN this way

UPDATE Teams P
INNER Join (SELECT 
          Team_Number
        , SUM(Points)  as team_sum 
      from week
      group by Team_Number ) t on t.Team_Number=P.Team_Number 
SET Session1Points = t.team_sum