[RESOLVED] using UPDATE in database

[msnhockey]

i created a csv importer for 3 files so my game stats updated alot quicker than manually doing it. in doing this i noticed after awhile that there was a bug in it and it was inserting the same info into multiple columns in the database

so right now i have some info in my schedule_scores table that i need to get fixed and i heard using UPDATE is a quick method

I am new to this so I tried moving players from one team to another and that worked fine, but this query is gonna be alot more involved i think

the 5 columns i need to fix are xtra1, xtra2, xtra4, goalsa, shotsa

so if the player is not a goalie i need the goalsa and shotsa set to 0
and if a goalie I need xtra1, xtra2 and xtra4 set to 0

so i think to get the player postition i have to reference the players table and look at the position column

I am also guess i might have to do 2 different queries for this one for non goalies and one for goalies

here is what i have so far and cant figure out how to make it look in the players table for if goalie or not

UPDATE player_stats
SET xtra1 = 0,
xtra2=0,
xtra4=0
WHERE xtra1 => 1 AND xtra2 => 1 AND xtra4 => 1

player_stats table info
id, goals, assists, goalsa, shotsa, minutes, xtra1, xtra2, xtra3, xtra4, xtra5, gminutes, played, game_id, team_id, div_id, seasonid, gseconds, leagueid

players table
id, lname, fname, teamid, position, number, dob, pob, weight, height, info

[msnhockey]

or would something like this be better

UPDATE player_stats ps
SET ps.xtra1 = '0', ps.xtra2 = '0', ps.xtra4 = '0'
WHERE exists
(SELECT p.playerid
FROM players p
WHERE p.position='G' AND
p.id = ps.playerid)

[msnhockey]

the above post worked for removing the goalie info from xtra1, xtra2 and xtra4

this worked for removing non goalie info from shotsa and goalsa

UPDATE player_stats ps
SET ps.shotsa = '0', ps.goalsa = '0'
WHERE exists
(SELECT p.playerid
FROM players p
WHERE p.position !='G' AND
p.id = ps.playerid)