MySQL update Syntax for statspage. Help needed.

[NeverUsedID]

Hi, iam very New to MySQL.

I want to enhance pwn's privateserver SQL dB with stats for Overall zombikills for example.

I have a dB character_data with column killsz where the zombieskills per survivor are stored and a dB player_data where i have killZTotal.

If a character is killed the column alive get the value 0. i can copy this survivors to another dB First. So i dont Need the alivecheck, but its nice to know anyway

NOW i want to count all killsz for survivors that have alive=0 and Group them by playeruid. After that i want to update player_data column killsztotal with this value where playeruid is the Same in both tables. As playeruid is not the primarykey i dont NOW how to do this.

Maybe there is a much better Way to get the totalnumbers of zombiekills.

If you have any examples It would be Great. I only want to use MySQL no php.

Thank you very much for some examples. I googled around and found many examples, but in all examples i have to know the playeruid.


now i need to update player_data with this result. I now how to insert, but not how to update the playeruid that are already there.

EDIT: Solved it this way. If some of the pros knows a better way let me know

CREATE TEMPORARY TABLE if not exists `countTotals`  (
    `playerUID` VARCHAR(45) NOT NULL DEFAULT '0',
    `KillsBTotal` INT(11) NOT NULL DEFAULT '0',
    `KillsHTotal` INT(11) NOT NULL DEFAULT '0',
    `KillsZTotal` INT(11) NOT NULL DEFAULT '0',
    `HumanityTotal` INT(11) NOT NULL DEFAULT '0',
  `distanceFootTotal` INT(11) NOT NULL DEFAULT '0',
  `HeadshotsZTotal` INT(11) NOT NULL DEFAULT '0',
    INDEX `playerUID` (`playerUID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
 
INSERT INTO countTotals (playerUID, KillsBTotal, KillsHTotal, KillsZTotal, HumanityTotal, distanceFootTotal, HeadshotsZTotal )  SELECT PlayerUID, sum(killsB), sum(killsH), sum(killsZ), sum(Humanity), sum(distanceFoot), sum(HeadshotsZ) FROM character_dead GROUP BY PlayerUID;
 
UPDATE player_data pd, counttotals ct
SET pd.killszTotal = ct.KillsZTotal, pd.killsHTotal = ct.KillsHTotal, pd.killsBTotal = ct.KillsBTotal, pd.HumanityTotal = ct.HumanityTotal, pd.distanceFootTotal = ct.distanceFootTotal, pd.HeadshotsZTotal = ct.HeadshotsZTotal
WHERE ct.PlayerUID = pd.PlayerUID;
 
DROP TEMPORARY TABLE countTotals;