Using CASE Statements In A SQL UPDATE Query
Posted August 30, 2007 at 7:35 AM
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:
Launch code in new window » Download code as text file »
- 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:
Launch code in new window » Download code as text file »
- <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!
Download Code Snippet ZIP File
Comments (11) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
Nice work Ben,
I always like to see people leaning on thier database a little to do this kind of work for them, many people would simply break that in to two queries, the first being a SELECT and then use CF conditionals to determine the update type.
I'm a big fan of having SQL do this stuff for you, as CF developers its very easy to forget that SQL is an entire language all of its own and is MASSIVLY powerfull for this data manipulation stuff, it took me quite a while to earn full respect for SQL as its own language when I first started developing, but once I did you start thinking about things in a very different way.
Rob
Posted by Rob Rawlins on Aug 30, 2007 at 8:00 AM
MySQL has a Greatest() function:
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest
I'm sure the other servers have something equivalent. Otherwise, you'd probably want to use tsql or a stored proc.
Posted by Todd Rafferty on Aug 30, 2007 at 8:25 AM
@Rob,
I know what you mean. SQL is awesomely powerful. I am still learning new things all the time. I still have yet to fully understand the whole CONVERT() function and I would love to learn more about Cursors and that sort of stuff.
@Todd,
It looks like MS SQL Server doesn't have the Greatest() equivalent, at least nothing that I could find on Google.
Posted by Ben Nadel on Aug 30, 2007 at 9:15 AM
@Ben: Yup, tsql it is. Anyway, your case statement works.
Posted by Todd Rafferty on Aug 30, 2007 at 9:23 AM
Another nice technique that I have found invaluable on occasion is using a join in an update statement. I don't know if something like this works on MySQL but on SQL Server you can do something like the following
UPDATE p
SET
p.col1 = p.col1 + q.otherCol
FROM table1 AS p join table2 AS q
ON p.fk_table2_pk = q.pk
WHERE
q.filterColumn = 'something'
Posted by JohnEric on Aug 30, 2007 at 9:33 AM
@John,
That is awesome! I knew you could do something like that on a View, but I had no idea you could just update with join usage in a standard statement. Crazy!
Posted by Ben Nadel on Aug 30, 2007 at 9:39 AM
@Ben/John,
I had just learned you could do joins in an UPDATE SQL statement early last month. I should have guessed you could since the FROM statement is there and joining would making sense if you need to bring data from other locations. I learned how to use it because I needed to move data from one database to another (QA to DEV). It's easy to get lost in SQL but it can really do some neat stuff.
Posted by Javier Julio on Aug 30, 2007 at 10:46 AM
It's amazing what SQL can do once you start digging into it.
A little gotcha about CASE statements is that you can only return simple values in your THEN clauses. It's a little less flexible than using dynamic CF in your queries, but as long as you are only looking to return simple values the CASE statement is very powerful.
Also check out IF and IFNULL. IFNULL is nice if for example, your column value is null but you want to return 0:
IFNULL(mycol,0) as col
This will return the column value if it is not null, or 0 if it is null. This can save a lot of extra CF coding if you happen to be in that situation.
Posted by Josh Nathanson on Aug 30, 2007 at 3:08 PM
When I say simple values I mean you can't do something like this:
CASE WHEN 1
THEN 'Where colval = 1'
ELSE
'Where colval = 2'
END
In other words you can't change the structure of the query the way you can using dynamic CF.
Posted by Josh Nathanson on Aug 30, 2007 at 3:11 PM
SQL can do many magical stuff.
I use SQL for all the business logic. I use ColdFusion for presentation.
CASE statement can be effectively used in ORDER BY :
http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/
People do create whole another SP (as another comment earlier) instead of using CASE.
I use other technologies with SQL but ColdFusion has been my favorite since day one.
Regards,
Pinal
Posted by Pinal Dave on Aug 30, 2007 at 7:26 PM
@John Eric,
I just tried your technique (UPDATE + JOIN) and it works like a charm! Very cool stuff:
http://www.bennadel.com/index.cfm?dax=blog:938.view
Thanks for the hot tip.
Posted by Ben Nadel on Sep 4, 2007 at 10:38 AM
