Post reply

Bitwise Operator (pipe) in Update Statement

  • August 26, 2015 at 11:28 am

    #398949

    I'm researching some long running queries and came across a query of this format:

    UPDATE t1

    SET inheritedmask = t1.inheritedmask | t2.mask | t2.inheritedmask | 0x08000000

    FROM table1 t1

    JOIN table1 t2 ON (t1.id = t2.id)

    where t1.objectid = @referencingobjectid

    AND t1.objecttypecode = @referencingobjecttypecode

    AND t2.objectid = @referencedobjectid

    AND t2.objecttypecode = @referencedobjecttypecode

    I've never come across a query with the bitwise operator in the SET part of the query and just curious about exactly what this is doing?

  • August 26, 2015 at 12:49 pm

    #1822601

    It sets the corresponding bits on, regardless of whether they were on before or not.

    For example:

    SELECT CAST(0 AS int) | 1 | 2 | 4

    SELECT CAST(1 AS int) | 1 | 2 | 4

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply