[SOLVED] Update All Rows With SUM from Another Table

[josborne]

First, I want to say that this is the most useful place anywhere for MySQL help. I have encountered problems that I would never have been able to solve without the help of the people here.

 

 

To my problem: I need to make an update to a table based on a calculation that is, in turn based on rows in another table.

 

I am thinking that showing what I am trying to do will make this easier to understand.

 

 

	CREATE TABLE `Player_tbl` (
`Player_ID` bigint(20) NOT NULL auto_increment,
`Player_Name` varchar(50) NOT NULL,
`New_Points` int(11) default NULL,
PRIMARY KEY  (`Player_ID`),
UNIQUE KEY `Player_Name` (`Player_Name`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1



CREATE TABLE `Unit_tbl` (
`Unit_ID` int(11) NOT NULL auto_increment,
`Estimate_ID` int(11) NOT NULL,
`Player_ID` int(11) NOT NULL,
`Unit_For` float(3,0) default NULL,
`Unit_Against` float(3,0) default NULL,
PRIMARY KEY  (`Unit_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=147 DEFAULT CHARSET=latin1

 

 

So, what I need to do is SUM Unit_tbl.Unit_For then add it to the SUM of Unit.Unit_Against for each Player_ID and then insert that into the Player_tbl.

 

Like this:

 

SET New_Points='100'-(SELECT SUM(Unit_For)+SUM(Unit_Against) FROM Unit_tbl 

 

Except that I need both Sums to be the sum for each player rather than for the entire column.

 

I hope this all make sense.

 

 

It may be that I am up too late and have been staring at this for too long but I have no idea how to solve it.

[Daniel0]

I'm not sure I understand, but maybe something like this:

 

UPDATE Player_tbl AS p
  SET p.New_Points = 100 - 
    (SELECT SUM(u.Unit_For)+SUM(u.Unit_Against)
     FROM Unit_tbl AS u
     WHERE u.Player_ID = p.Player_ID);

[josborne]

That did it. Thanks a lot.