Nothing revolutionary here, I just recently tried to use a SQL CASE statement as part of an SQL UPDATE statement. I had never tried this before and I am quite happy that it works. I am working on a transactional system that allows for the voiding of account credits. As part of the Void process, I need to make sure that I never let an account balance drop below zero dollars (business requirement - these aren't "real" dollars). In something like ColdFusion, I would use the Max() function:
Max( 0, (balance - void_credit) )
But, in SQL, the MAX() function performs aggregate calculations on groups, not "max of two numbers" calculations. As such, I tried the following CASE statement:
<cfquery name="qUpdate" datasource="xxx"> UPDATE [account] SET balance = ( CASE WHEN ((balance - 10.00) < 0) THEN 0 ELSE (balance - 10.00) END ) WHERE id = 1 </cfquery>
I am hard coding the variables here, but you get the point. I think it's kind of cool that this works. I guess there's nothing about it that should have made me think that it wouldn't work; I just never tried this technique before. I love the fact that you can reference values in the database row that you are going to update. Very cool!
Want to use code from this post? Check out the license.