UPDATE with SUM(column)

[hilltopper06]

I am trying to update records using the SUM function, but NULL is being returned (even though I know that values should have been found).  Here is my code:

 

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");

mysql_select_db($dbname);

$sql = "update pdreg a, pdprog b SET a.PDHours = b.Hours WHERE a.ProgNum = b.ProgNum";

mysql_query($sql);

$sql = "update pdedu a, pdreg b SET a.PDHours = SUM(b.PDHours) FROM pdreg WHERE a.TeaSSN = b.TeaSSN";

mysql_query($sql);

mysql_close($attn);

 

The first query sets the field PDHours (in table pdreg) to the appropriate number of hours as seen in the pdprog table.

 

The second query is supposed to total the hours in the pdreg.PDHours table and have pdedu.PDHours equal to it, where the TeaSSN's match up.  The problem is that I cannot get the SUM function to work right with the WHERE statement.  Can anyone shed any light on this problem?  Thanks.

[redarrow]

where the or die("mysql error".mysql_error());

 

have you debugged what?

 

have you even echo the query's out to see if there correct?

[hilltopper06]

Ok, I did some messing around in the MySQL Console and this command is returning what I am looking for:

 

SELECT SUM(PDHours) FROM pdreg GROUP BY TeaSSN;

 

It returns a long list of the total hours for each teacher.  Now I am not sure how to take that information and have it update the appropriate field in the pdedu table.  Any ideas?

[hilltopper06]

Bump.

 

I still have not figured this out.  Anyone?  Thanks.