Jump to content

[SOLVED] Update All Rows With SUM from Another Table


josborne

Recommended Posts

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.