Insert data into table and update another table with that data.

Question

I'm trying to achieve the following:

When I add into the table Players_goals that player_John has scored 3 player_goals in match_xx - That it doesn't just get inserted into the Players_goals table, but also into the Players table. And if player_John had already scored 2 goals, that it gets updated to 5 (sum).

Please see the tables I'm using below:

Players table
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| player_id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| player_name  | varchar(45) | NO   | MUL | NULL    |                |
| team_name    | varchar(45) | NO   | MUL | NULL    |                |
| player_goals | int(4)      | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Players_goals table
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| player_name | varchar(45) | NO   | MUL | NULL    |       |
| match_id    | int(4)      | NO   | MUL | NULL    |       |
| goals       | int(4)      | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

How can I make this possible?

Answer 1

below is just dummy code to understand the logic and you can go through this

CREATE TRIGGER goalTrigger

AFTER INSERT ( into player_goals  table )

FOR EACH ROW 
BEGIN

INSERT INTO (player table ) VALUES()
ON DUPLICATE KEY UPDATE player_goals = player_goals+current_goles

END

REFERENCES

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

Answer 2

I would set up a trigger on the ONINSERT event of Players_goals that updates the relevant row of Players

Answer 3

I assumed that player_goals in Players table is sum of all goals the player scored in every match until recently. If that is a case:

first player_goals is a redunandant data you have to avoid, you can get this information from table Players_goals by joining the two tables.

second: if you have to do that though, you can insert such information to players_goal with INSERT statement and into players table using UPDATE statement, in which case either you have to first get all sum goals by the player from Players_goals or you have to have a way to increment the old value of player_goals in Players table