SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Bristol, UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining UPDATE statements with different fields and conditions

    Hi,

    I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query:

    UPDATE table SET field1 = field1 - n WHERE field 1 > x
    UPDATE table SET field2 = field2 - n WHERE field 2 > x


    Any help greatly appreciated,
    thanks,
    Ben

    [EDIT: just noticed x and y can be the same]
    Last edited by aramando; Feb 16, 2007 at 12:04. Reason: updating requirement

  2. #2
    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)
    Code:
    update table
       set field1 = case when field1 > x
                      then field1 - n
                      else field1
                    end
         , field2 = case when field2 > x
                      then field2 - n
                      else field2
                    end
     where field1 > x
        or field2 > x

  3. #3
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Bristol, UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    fantastic, thanks a lot.


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
  •