If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Update 1 condition across multiple columns

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 3
Update 1 condition across multiple columns

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?
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,658
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.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 3
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.
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,658
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
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 3
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On