group by, insert/update, where

Hi everyone,

I'm trying to figure out a query here and I'm a little bit lost as to where I'd be placing the Where query in my attempt.

What I'm trying to achieve is to query one table where all network_points are grouped by uid_owner and then moved to the uid_table (current_points) where uid_owner equals the uid

INSERT INTO uid_table (current_points)
SELECT sum(network_points)
FROM nid_table
GROUP BY uid_owner";

Code (markup):

What the above does, it totals based on uid_owner and then moves to the uid_table, but inserts these as new records with the totals. What I'm missing is the where statement or another comparative option.

Can anyone assist with this?

 

Are you just trying to update the value in the uid_table?

You would want to use an update statement instead of an insert statement, if I am understanding the problem correctly.

Something like this (Untested):

UPDATE uid_table uid
INNER JOIN
(SELECT uid_owner,
Sum(network_points) AS total_points
FROM nid_table
GROUP BY uid_owner) nid ON uid.uid = nid.uid_owner
SET uid.current_points = nid.total_points

Code (markup):

 

This was the perfect answer. I'm not familiar enough to fully understand joins. Fair comment as well on using update instead of insert. Thanks for the help though Jestep, as I've been able to mod this a bit further to get some other things performed in a cron job on the system I'm setting up.

Thanks agian.