Bitwise Operator (pipe) in Update Statement

• Samwell

  Mr or Mrs. 500

  Points: 579

  More actions

  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?

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  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) A socialist is someone who will give you the shirt off *someone else's* back.