SQL Server: Updating Integer Status Columns

Thursday Dec 30th 2004 by Gregory A. Larsen
Share:

The first article of this series, discussed how to store different statuses in a single integer column. This article expands on the topic of using integer columns for storing statuses and discusses how to update the integer status column to change or remove any given status.

In my first article in this series, I discussed how to store different statuses in a single integer column. In this article, I will expand on the topic of using integer columns for storing statuses and will discuss how to update the integer status column to change or remove any given status.

Prior to getting into how to update a status column, let us first review how an integer column actually stores multiple statuses. The numerical value of an integer column can be represented a number of ways. One of those ways is to represent an integer as a series of 1's and 0's; I refer to this as the binary format of an integer. An integer is 4 bytes long, and each byte can contain up to 8 bits. Therefore, a single integer column can represent 31 different status values. Let us look at a simple example. Say an integer column contains the integer value 140. The binary representation of this number is 0000000000000000000000010001100. This binary number has 3 bits that are set to 1, and 28 bits that are set to 0. Each bit that is set to 1 indicates that the status associated with that bit is set to TRUE, where as if the bit is set to 0 then the status for that bit is set to FALSE. Each one of the 31 different bits of the above binary value would represent a different status.

Now that we know how an integer value can store different statuses using a binary string of 1's and 0's, let us look at an actual table that will store multiple records, where each record will contain an integer status column. I am going to use the same table and statuses as in my first article. Here is the code to create my table:

create table Student (StudentName varchar(10), Status int)

Here is a matrix that represents all the possible statuses for a given student:

Status

Integer Value

Late Arrival

1

Early Dismissal

2

Honor Society Member

4

Member of the Band

8

Member of the Choir

16

Teachers Aid

32

Student Mentor

64

Running Start

128

I will need to populate my Student table with some records so I can use these records later on to perform updates. The matrix below shows the different statuses for each student record that I will be inserting into my Student table:

Student

Status

Fred

late arrival, early dismissal

Barney

honor society member, member of the band, Running Start

Wilma

member of the choir, teachers aid, student mentor

Dick

member of band

Jane

Here is the code I use to add these Student records:

insert into Student values ('Fred',1|2)
insert into Student values ('Barney',4|8|128)
insert into Student values ('Wilma',16|32|64)
insert into Student values ('Dick',8)
insert into Student values ('Jane',0)

Now that I have my "Student" table populated, we can start looking at how to update statuses.

Removing A Particular Status

Say I what to update the status of Student "Fred" so he no longer has "Early Dismissal." To do this I would need to set the second bit of the Status column to a "0," since the second bit represents the status for "Early Dismissal." In binary math I would need to AND a "0" to second bit in order to remove the "Early Dismissal." Here is the code I use to accomplish that:

update Student
  Set Status = Status & 0xFFFFFFFD
  where StudentName = 'Fred'

Here you can see I ADD'd a "0XFFFFFFFD" to the value of Status column for the Student record where the StudentName is "Fred". The binary representation for the hex number "0xFFFFFFFD" is '11111111111111111111111111111011'. This binary number only has the second bit set to zero. Therefore, when this number is AND'd to the status column only the second bit would be set to zero, regardless of what the other bits are set to.

Now say the school abolishes the band, I would need to update the status for all students that are in the band. To accomplish this I would need to set the 4th bit in the integer status column to a zero, since the 4th bit is the bit that represents a student is in the band status. Here is the UPDATE statement to change the status of all student records, so no student has a status of "member of band."

select * from Student 
update Student
  Set Status = Status & 0xFFFFFFF7

Here only the 4th bit in the AND'd value "0xFFFFFFF7' is set to a zero. Therefore, when this UPDATE statement is executed it will only change the status value if the student is a member of the band.

Performing an ADD function against the status column where the AND'd value is a hexadecimal number is not easy to read to determine which status is being removed. So let me show another way that is easier to read. To remove the "Early Dismissal" from student "Fred" I can run the following code:

update Student
  Set Status = Status - 2
  where StudentName = 'Fred'

Here I have subtracted the value "2" from the integer Status value of Fred's Student record. Since the status of "Early Dismissal" has an integer value of "2," this subtraction method works to remove the "Early Dismissal" status. This subtraction method will only work if the record being updated does in fact have the specific status you are trying to remove set. In my example student, "Fred" does in fact have the status value "2" (Early Dismissal) set. To further hammer this point home, let me try to use this same method to remove the "member of band" status from all students to see what happens. Assume I run the following set of statements to remove the "member of band" status from all students.

select * from Student
update Student
  Set Status = Status - 8
select * from Student

When I run these commands, I get the following output:

StudentName Status      
----------- ----------- 
Fred        1
Barney      140
Wilma       112
Dick        8
Jane        0

(5 row(s) affected)

(5 row(s) affected)

StudentName Status      
----------- ----------- 
Fred        -7
Barney      132
Wilma       104
Dick        0
Jane        -8

(5 row(s) affected)

As you can see, this command did in fact remove the "member of band" status from Students Barney and Dick, but that is not all that it did. By reviewing the statuses for the students that where not in the band, you can see that this update totally messed up some of the students status value. Therefore, this particular update method should not be used to remove a status unless the records being updated do in fact have that status set for the status you wish to remove.

I can code around this problem of updating records that do not have a particular status set. Review the following code:

update Student
  Set Status = Status - 8
 where status&8 = 8

Here I have added a WHERE clause to my original UPDATE statement. Now this UPDATE statement will only update those students that are in fact a member of the band.

Adding a Particular Status

Now say a student decides to change their status by starting to have "Early Dismissal." In this case, you would need to update that student's Status column to reflect that they have "Early Dismissal." It is very easy to perform this kind of status update.

Suppose that Dick is the student that wants to change his status and start leaving school early. I can update the Status column on Dick's Student record by performing the following UPDATE statement:

update Student 
  set Status = Status|2
  where studentName = 'Dick'

Here I have used the OR function to update the Status column on Dick's Student record. By OR'ing a "2 " with the original status of Dick's Student record, I have set the second bit to a "1", thereby setting the status to "Early Dismissal."

Now suppose the school changes a policy and now all students are required to be a "Teachers Aid." I can update all my Student Records to have their status reflect that they are a "Teachers Aid" by performing the following UPDATE:

update Student 
  set Status = Status|32

Here I have OR'd the value 32 to the Status value on all student records. This UPDATE statement assigns the status of "Teachers Aid" to all students, regardless of whether they are already a teachers aid or not.

Conclusion:

Updating and adding new statuses is not that difficult. Although one needs to be careful on how they update statuses, so as not to mess up the status column. I hope that this article has given you some ideas on how you can use, update and add multiple statuses using a single integer column and some binary math.

» See All Articles by Columnist Gregory A. Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved