A little gotcha about CASE statements is that you can only return simple values in your THEN clauses. I have many stored procedures update the same table but different fields, and I am thinking to merge them into one sproc.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. 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. CASE statement can be effectively used in ORDER BY : do create whole another SP (as another comment earlier) instead of using CASE. By doing so, the front end developer can just call the same sproc and do several update processes by varying the keywords.I am working on a transactional system that allows for the voiding of account credits.

clause; columns not explicitly modified retain their previous values.

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the you should ensure that the join produces at most one output row for each row to be modified.

It's easy to get lost in SQL but it can really do some neat stuff.

It's amazing what SQL can do once you start digging into it.

This can save a lot of extra CF coding if you happen to be in that situation. I use other technologies with SQL but Cold Fusion has been my favorite since day one. @Goutam, Take a look at this post: post is for MS SQL, I am pretty sure, but My SQL supports the same thing with a slightly different syntax.

When I say simple values I mean you can't do something like this: CASE WHEN 1THEN 'Where colval = 1'ELSE'Where colval = 2'ENDIn other words you can't change the structure of the query the way you can using dynamic CF. Regards, Pinal Hi, What if I want to "switch" the field name? You might just have to Google the My SQL specific syntax - the difference is just where the table join is actually performed.In other words, a target row shouldn't join to more than one row from the other table(s).If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.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.In something like Cold Fusion, I would use the Max() function: I am hard coding the variables here, but you get the point. 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.