Prev: what is that?
Next: FakeWorkTable error
From: Sammy on 17 Jun 2007 09:06
Hi if I have a bit column for a PhoneNumberType using binary to represent the
values as below and need to do some updates

home = 2
Fax = 4
mobile = 8
office = 16


Not sure the logic involved. If some one just had a home number and that = 2
and now also had Fax. Would I just update the value to 6 to reprensent these
changes. Or is there a better syntax to use rather then

update Contact
set PhoneNumberType =6
where contactid ='1020' ---contactid value

Thanks for any help

Sammy





From: Erland Sommarskog on 17 Jun 2007 09:47
Sammy (Sammy(a)discussions.microsoft.com) writes:

> Hi if I have a bit column for a PhoneNumberType using binary to
> represent the values as below and need to do some updates
>
> home = 2
> Fax = 4
> mobile = 8
> office = 16
>
>
> Not sure the logic involved. If some one just had a home number and that
> = 2 and now also had Fax. Would I just update the value to 6 to
> reprensent these changes. Or is there a better syntax to use rather then
>
> update Contact
> set PhoneNumberType =6
> where contactid ='1020' ---contactid value

UPDATE Contact
SET PhoneNumberType = PhoneNumberType | 4
WHERE contactid = '1020'

| is a bitwise-or operator.

The design as such is dubious, although there are situations where storing
data as bit masks make sense.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: David Portas on 17 Jun 2007 14:12
On 17 Jun, 14:06, Sammy <S...(a)discussions.microsoft.com> wrote:
> Hi if I have a bit column for a PhoneNumberType using binary to represent the
> values as below and need to do some updates
>
> home = 2
> Fax = 4
> mobile = 8
> office = 16
>

A very silly design in my opinion. Why would you want to do it that
way? Apart from the obvious disadvantage of manipulating binary data
in SQL, you are actually encoding at least two different pieces of
information here: technical attributes of a phone number (such as
"fax" or "voice") and phone location ("home" or "office").

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 | 
Pages: 1
Prev: what is that?
Next: FakeWorkTable error