SQL Server: Updating Integer Status Columns - Page 2

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