Results 1 to 5 of 5
  1. #1
    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,525
    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 on the Tiger 800 or the Norton

  3. #3
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,525
    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 on the Tiger 800 or the Norton

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •