Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24

    Unanswered: Update multiple rows/values in one query?

    It is my understanding that some databases allow an UPDATE statement that will update multiple rows with different values. Is SQL Server one of them, and if so, what's the syntax?

    I have to code a page that displays the (exactly) four items in a database table, where each one has a dropdown widget to allow the user to specify an ordering position. Obviously I can process this in the ASP code by looping through and creating one UPDATE statement per dropdown field, but it seems it'd be easier on the database server if I could do it in one query rather than four. Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hi michelle (we meet again, eh)

    yes, one statement, four fields, one row, typically the primary key is a hidden field:

    update yourtable
    set field1 = dropdown1value
    , field2 = dropdown2value
    , field3 = dropdown3value
    , field4 = dropdown4value
    where pkey = hiddenfieldid

    rudy

  3. #3
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24

    My mistake, let me clarify...

    Rudy, you are everywhere. A veritable Guardian Angel of Web Development Help :D

    I should have been clearer in my original posting; the four dropdowns are each attached to an individual row. So I want to update four rows with one UPDATE, if such a thing is possible.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you know which rows? 4 hidden id fields?

    better use 4 statements


  5. #5
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24
    The ids are embedded in the names of the dropdown boxes when that form is generated via ASP.

    Maybe now is a good time to say I didn't design this functionality, am merely coding it :D

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    ASP ? ADODB ?

    rs.LockType = adLockBatchOptimistic
    rs.BatchUpdate

    OR T-SQL ?

    update tbl set
    A=case [id] when 5 then 15 when 6 then -6 end
    where [id] in (5,6)

  7. #7
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24
    ispaleny, that's very slick, I'll have to try that out. Thanks!

Posting Permissions

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