SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    Chicago, USA
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating Multiple Rows @ Once

    How would I use one UPDATE query to update every row with different values? Like so:

    Code:
    UPDATE table SET column = 'value1' WHERE id = 1
    UPDATE table SET column = 'value2' WHERE id = 2
    UPDATE table SET column = 'value3' WHERE id = 3
    And so on...

    How would I acheive this with using one query? Or is this not possible? Thanks.

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    Chicago, USA
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone?

  3. #3
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been wondering about the exact same thing but I think you can't do it, I mean, how would you update many rows that hold many different values with one query, this just doesn't make sense if you really think about it.

    I could be awfully wrong though
    - website

  4. #4
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by website
    I could be awfully wrong though
    I don't think you are.

    What's perfectly possible of course is to update several rows with the same value.

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    Chicago, USA
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Zoef
    I don't think you are.

    What's perfectly possible of course is to update several rows with the same value.

    Rik
    Hmmm, guess I'll have to do 50 queries then. : \

  6. #6
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if the value follows some pattern you *could* place it in some loop ...
    - website

  7. #7
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Which would still be 50 queries, just not 50 separate SQL queries to type out.
    Ian Anderson
    www.siteguru.co.uk

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this will work in MySQL, but:
    Code:
    UPDATE table SET col1 = CASE id
                              WHEN 1
                                THEN 'foo1'
                              WHEN 2
                                THEN 'foo2'
                            END

  9. #9
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    I don't know if this will work in MySQL, but:
    Code:
    UPDATE table SET col1 = CASE id
                              WHEN 1
                                THEN 'foo1'
                              WHEN 2
                                THEN 'foo2'
                            END
    I guess it just might, 'CASE' is mentioned in the manual:
    http://www.mysql.com/documentation/m...flow_functions

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac

  10. #10
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    Which would still be 50 queries, just not 50 separate SQL queries to type out. [img]images/smilies/wink.gif[/img]
    I know that
    - website

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    Chicago, USA
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah cool, I'll give it a try. Thanks for your suggestion...


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
  •