Trigger to update sum from another table MySQL
<p>I have a database which keeps track of stats and averages for a league i'm in. I have a results table and a standings table, where I add the "points" from results to an overall "points" in standings. I currently have a query which successfully adds the points from the results to the standings table. However, when I add new results, the standings doesn't update.</p>

<p>Here is my query which I use to SUM the points from the results to standings.</p>

<pre><code>UPDATE standings s,( SELECT team_id, sum(points)  as mysum
FROM results GROUP BY team_id) as r
SET s.points = r.mysum
WHERE s.team_id = r.team_id;
</code></pre>

<p>I'm attempting to use a trigger to update the overall points automatically.  I'm new to triggers, and am receiving syntax error #1064 when I run the following.</p>

<pre><code>CREATE TRIGGER standingsUpdate
AFTER INSERT ON results
FOR EACH ROW
BEGIN
    UPDATE standings s,( SELECT team_id, sum(points)  as mysum
    FROM results GROUP BY team_id) as r
    SET s.points = r.mysum
    WHERE s.team_id = r.team_id;
END
</code></pre>

<p>Would anybody be able to point me in the right direction? Is there a better way to accomplish this?</p>

<p>Thanks,
- Jason</p>