I have inherited a league data base.

I have discovered that there are repeated players.

Table Player
Player ID Player_first Player_last
5 Joe Smith
105 Joe Smith

I would like to create a query that will check in the Games Table in each of the columns Player1,player2,player1_opp, player2_opp , see if the number equals 105 then change it to 5.

Table games
Game_ID Player1 Player2 Player1_opp Player2_opp
123 5 <null> 8 <null>
456 54 <null> 5 <null>
1222 62 5 84 92
664 34 92 12 105
774 105 <null> 8 <null>


Not how sure to do this in 1 query, will this need to be done in a script?

Q1: update mytable
set player1 = 5
where player1 = 105
Q2: update mytable
set player2 = 5
where player2 = 105

OR
create a temporary table called say
PlayerDuplicates
PlayerIDAsIs
PlayerIDShouldBe

populate that table as required
eg
105 5

then join that table to the revised Q1 & Q2
RQ1
update mytable set player1 = PlayerDuplicates.PlayerIDShouldBe where player1 = PlayerDuplicates.PlayerIDAsIs

RQ2
update mytable set player2 = PlayerDuplicates.PlayerIDShouldBe where player2 = PlayerDuplicates.PlayerIDAsIs

in essence
PlayerDuplicates stroes details of whch players have been duplicated and defines what the duplicate ID is, and what it shoudl be

RQx looks to find if there is a match for the duplicateID in your table (mytable) and replaaces the duplicate ID (AsIs) with the new ID (ShouldBe)



however as Q1 & Q2 modify your data youmay want to run this on a copy of your tables (untill the SQL is proven right and you are sure it does what you want.

or you could be a smarty pants and create new columns which will hold the new values, once you are happy the new values are correct then update the original column with the revised column. the advantage of doing it in such a wasy is that until you decide the new data is correct you are not comitted to using it. if you run Q1 & Q2 it wilol overwrite the data meaning if there is a problem then you loose data.

Thanks for the quick reply,

So to clarify, this can't be done in just one query? Just checking to verify the limits of the update queries.

As far as the temporary table....that is the next think I will need to learn how to do.

as of now, I am simply doing a dump, make changes, check, leave as is or restore as needed.

At some point I would like to write a script in php that I will be able to

One select 2 names and player IDs
Generate a "count" summary of each ID.
Then merge the 2 ID's into 1.
But I am still fairly new and have a ways to go (like becoming proficient with MySql and learnign html and php from scratch) before talking that.

id argue the first thing to do is to work out why / how you are getting duplicate player rows created
then change your design / procedures / software so it cannot happen again, then fix your data

Simply put,
I inherited the job.

I went to review the existing Database and found things such as Joe Smith and Jo Smith.

Or Bob Smith in league A but a different Bob Smith in League B

There have been tools put in place since these errors were corrected, but I am self-tasking the job to clean up the errors.

Thanks fo the advice though, there are only 4 or 5 redundancies in this DB that I am aware off so the previous guy, IMO, has done an outstanding job over the past 7 years.

Discalimer: he was dependant upon people filling out sheets while they were :drunk: at their bar.