Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Red face Unanswered: Partially Updating a BINARY(5) Column

    Hi people, this is about updating a BINARY(5) column. Say I have the following [Customers] table definition:

    HTML Code:
    CustomerId [INT]
    Flags [BINARY(5)]

    And it has got the following row of data:

    HTML Code:
     CustomerId |     Flags
    ------------|---------------
        1234    |  0x1122334455

    What should I do, in order for me to partially update the value of "Flags"?
    Say, if I want to change the second byte to produce 0x1199334455?

    HTML Code:
    UPDATE [Customers]
    SET ???? <--- What needs to be here?
    WHERE CustomerId = 1234

    Thanks in advance for your help (I've searched, and searched, but could not get much out of Google for now).

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    One way...


    Code:
    declare @customers table (CustomerId INT,
    Flags BINARY(5))
    
    insert into @customers values(1234,0x1122334455)
    
    select * from @customers
    
    update @customers
    set flags = 0x1199334455
    
    select * from @customers

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Don't they teach bitwise operators in universities anymore?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Nov 2009
    Posts
    9
    Thanks corncrowe, but that isn't quite what I'm after.

    Hey Nick, that's a good point (somehow I overlooked simple stuff like that). Thanks. So this is what I can do:

    Code:
    UPDATE [Customers]
    SET Flags = ((Flags & 0xff00ffffff) | 0x0099)
    WHERE CustomerId = 1234
    Only question is, what if later I change "Flags" to BINARY(7)? I then need to update my SQL statements above (since 0xff00ffffff will mask out the two newly added bytes towards the end).

    Maybe the following can help (without changing the codes when expanding to BINARY(7)):

    Code:
    UPDATE [Customers]
    SET Flags = ((Flags & (~0x00ff)) | 0x0099)
    WHERE CustomerId = 1234
    If that works, still there is one additional question, what if the field is "BINARY(100)" and I need to update the 77th byte? I can't be doing 0x00......ff......00 right? There must be a more precise way than this.


    Thanks again!

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Ben Goh View Post
    what if the field is "BINARY(100)" and I need to update the 77th byte?
    If that is a possibility, then you need to rethink your data model.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2009
    Posts
    9
    Right. That kinda...... helped.

  7. #7
    Join Date
    Nov 2009
    Posts
    9
    That "SET Flags = ((Flags & (~0x00ff)) | 0x0099)" did not quite work, Nicky. Gotta know more than just bitwise operators if one needs to be database consultant.

    I'm gonna dig around for an answer to that, and post it here when one is found. Meanwhile if anyone else knows the answer, please feel free to share

    Cheers!

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Ben Goh View Post
    That "SET Flags = ((Flags & (~0x00ff)) | 0x0099)" did not quite work, Nicky.
    It was your idea, Benny, not mine, and it's obviously incorrect.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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