SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with Conditional Update Statement

    I have the following statement:

    Code MySQL:
    UPDATE 
        tournaments
    SET 
        tournament_player1 = IF (tournament_player1 = 5, 1, 0), 
     
        tournament_player2 = IF (tournament_player2 = 5, 1, 0),
     
        tournament_player3 = IF (tournament_player3 = 5, 1, 0),
     
        tournament_player4 = IF (tournament_player4 = 5, 1, 0)
     
    WHERE
        tournament_time = 1 AND tournament_game = 1

    This statement checks if each field is 5 then updates the field to 1, but if the field is not 5 then it updates the field to 0. I want to make it so the field is left alone if the field is not 5. How can this be done?

  2. #2
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the solution:

    Code MySQL:
    UPDATE 
        tournaments
    SET 
        tournament_player1 = IF (tournament_player1 = 5, 1, tournament_player1), 
     
        tournament_player2 = IF (tournament_player2 = 5, 1, tournament_player2),
     
        tournament_player3 = IF (tournament_player3 = 5, 1, tournament_player3),
     
        tournament_player4 = IF (tournament_player4 = 5, 1, tournament_player4)
     
    WHERE
        tournament_time = 1 AND tournament_game = 1

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you might want to look at the CASE statement instead, which is standard SQL, while IF() is not.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

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