SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE multiple rows using GROUP BY?!

    I'm trying to update multiple rows by using GROUP BY but something is wrong?

    In my db table I always have 2 rows with the same game_id like this:

    id fk_game_id invite accepted

    1 34 1 0
    2 34 0 0
    3 57 0 0
    4 57 0 0
    5 89 0 1
    6 89 0 1
    7 92 0 0
    8 92 0 0
    9 96 0 0
    10 96 1 0

    Now, what I want is to update all fk_game_id where none of them has 1 in invite, in this case the games with fk_game_id 57 and 92.

    I have tried this but with no luck:
    PHP Code:
    $sql mysql_query("SELECT * FROM ".$prefix."_test_gameplayer WHERE invite=0 AND accepted = 0 GROUP BY fk_game_id");
    while(
    $row mysql_fetch_assoc($sql)){ 
        
        
    $thisgame $row['fk_game_id'];
        echo 
    $row['fk_game_id'].' - Invite: '.$row['invite'].'<br>';
        
    mysql_query("UPDATE ".$prefix."_test_gameplayer SET accepted=1 WHERE fk_game_id=$thisgame");
        
        

    Please help and thanks in advance :-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your GROUP BY approach is fine, if its purpose is to identify the fk_game_id which needs updating

    and your UPDATE statement is okay, if its purpose is to update ~both~ rows to accepted=1 for that fk_game_id

    however, the SELECT with GROUP BY is not returning an accurate result set

    for example, look at rows 9 and 10 --
    Code:
     9 96 0 0
    10 96 1 0
    row 9 will, by itself, be accepted by the existing WHERE clause (while row 10 will not)

    thus when you do the GROUP BY, row 9 will generate 96 as one of the fk_game_ids that come out of the SELECT

    here's what i think you should try --
    Code:
    SELECT fk_game_id
      FROM ...
     WHERE accepted = 0
    GROUP
        BY fk_game_id
    HAVING MAX(invite) = 0
    please let me know how that works out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It worked... Thanks. You rock ;-)


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
  •