UPDATE query

[esiason14]

Hi, I currently have two tables: hitterstats and pitcherstats. I would like to combine these into one table called playerstats.

Here's my dilemma. Some pitchers (all of whom, obviously, have stats in the pitchers stats table) also have hitting stats in the hitter_stats table, but some dont. So, i need something like this.

If the pitchers player_id exists in the hitterstats table for a certain year, then insert the stats into that yearly record. If there is no record for that player_id, then create a new record for that pitchers players_id. im having trouble coming up with this one, so any advice would be appreciated.

[pete_bisby]

The following SQL query will insert the pitcherstats player_ids that do not already appear in the hitterstats table. So, for example, you had 1,2,3,4 as player_ids in hitterstats, and 3,4,5,6 as player_ids in pitcherstats, this query would give you 5 and 6.

INSERT INTO hitterstats (player_id)
SELECT pitcherstats.player_id
FROM pitcherstats LEFT JOIN hitterstats ON pitcherstats.player_id=hitterstats.player_id
WHERE hitterstats.player_id IS NULL;