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