Have you ever wanted change the values in your database table from “on” to “off” or “true” to “false” using one compact SQL Statement? Well I did and thus began my journey …
As a long time ColdFusion developer I can remember times when I was presented with the following requirement;
I have a page that displays the results of a DB query. Those results are displayed in a table showing several columns. One of the columns contain a checkbox which, is used by the user to select one or more rows to be activated or deactivated. The user should be able to check one or more rows and click on a button that will execute a process to reverse the flag values for the rows selected. Each checkbox contains the value of the ID of its row.
Now, in the past I may have approached this solution by placing a SQL SELECT and UPDATE statement inside a CFQUERY inside a loop. If the user selected 10 rows then the process would hit the database twice for each row selected – once to get (SELECT … WHERE …) the current value of my flag column and then again to change (UPDATE … SET …) the value based on the value returned from the SELECT statement. It worked but boy was it so very inefficient.
Well, that was NOT going to be the case this time (case … heh … remember that now). So, I set about looking for my own “Holy Grail” (OK smarty … remember, one guy’s simple solution may be another guy’s … you get the point). I enlisted the help of a co-worker of mine (JoAnn) and Voila! she found it. So without further adu, I present to you … the solution:
SET tblMyTable.ActiveBit =
CASE tblMyTable.ActiveBit WHEN 1 THEN 0 ELSE 1 END
WHERE tblMyTable.ID IN (#CBSelections#)
So what’s happening here? We used a CASE statement in our SQL query to determine if the value of ActiveBit (a Bit data type column) is 1. WHEN the column value equals 1 THEN we set the column to 0 ELSE we set the column value to 1 – the END
The WHERE clause restricts our UPDATE only to those rows that have ID that are IN the CBSelections list. Selected checkboxes will return a list of its values separated by commas.