SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    updating many fields in same table...

    say i have one table with several fields, and want to update one field many times with different values

    Code MySQL:
     
    UPDATE sample_table SET field_one = '$val_one' WHERE member = '$member_one' AND id = '$id';
     
    UPDATE sample_table SET field_one = '$val_two' WHERE member = '$member_two' AND id = '$id';
     
    UPDATE sample_table SET field_one = '$val_three' WHERE member = '$member_three' AND id = '$id';
     
    .
    .
    .

    I am excuting the UPDATE operation inside a loop, so I need to figure out how to do it in just one query.

    I know how to do it with INSERT.

    Code MySQL:
     
    for($i=0; $i < 10; $i++){
     
      $val = ...;
      $member = ...;
     
      $comma = ($i == 9)?'':',';
     
      $insert .= "('$val','$member','$id')".$comma;
     
    }
     
    $q_insert = "INSERT INTO sample_table VALUES".$insert."";

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem is not that you are updating many columns in the same row, but the same column in many rows

    go ahead and use separate UPDATEs for separate rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the problem is not that you are updating many columns in the same row, but the same column in many rows

    go ahead and use separate UPDATEs for separate rows
    did u mean i must UPDATE many times ONE by ONE?

    Code MySQL:
    for($i=0; $i < 10; $i++){
     
      $val = ...;
      $member = ...;
     
      mysql_query("UPDATE sample_table SET field_one = '$vale' WHERE member = '$member' AND id = '$id'");
     
    }

    there are 10 queries here...mean 10 round trip to DB?

    may be I can DELETE all the rows with id = '$id', then insert like i mentioned.

    so there will be only 2 queries, DELETE and INSERT.

    i will get the same thing afterall.

    your advice?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,184
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    If a unique key exists on member and id you can achieve what your trying to accomplish using an insert with a duplicate key update in a single query.

    Code SQL:
    INSERT IGNORE INTO sample_table (member,id,field_one) VALUES ('member_one',1,'val_one'),('member_two',2,'val_two'),('member_three',3,'val_three') ON DUPLICATE KEY UPDATE field_one = VALUES(field_one)

  5. #5
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    If a unique key exists on member and id you can achieve what your trying to accomplish using an insert with a duplicate key update in a single query.

    Code SQL:
    INSERT IGNORE INTO sample_table (member,id,field_one) VALUES ('member_one',1,'val_one'),('member_two',2,'val_two'),('member_three',3,'val_three') ON DUPLICATE KEY UPDATE field_one = VALUES(field_one)
    thank you very much! will try that out. deleting and inserting again is not an option since my real world table has many fields with datas that must be retained.

  6. #6
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry that doesn't work in my case. look like it simply doing what INSERT does.

    these are the 2 fields that identify which row to update, where field `id` has the same value -- $id

    WHERE member = '$member_one' AND id = '$id';
    WHERE member = '$member_two' AND id = '$id';
    .
    .
    .

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,184
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    What column(s) on said table is a primary or unique key?

  8. #8
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    What column(s) on said table is a primary or unique key?
    sorry that table has no primary/unique key but field `id` as index only

    CREATE table sample_table (
    field_one...
    field_two...
    field_three...
    member...
    id....
    .
    .
    .
    INDEX(id))TYPE=MyISAM"

    it is not possible to create unique, even primary key i think.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,184
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Then there is no other way to achieve what you want without multiple queries. Why isn't id a primary key? You should have a primary key defined on table…

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Assuming the ID field is an auto-increment why not make it the primary key?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •