Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with:

Using CASE Statements In A SQL UPDATE Query

By Ben Nadel on
Tags: SQL

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!



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

Reply to this Comment

@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.

Reply to this Comment

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'

Reply to this Comment

@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!

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

Hi,

What if I want to "switch" the field name? Your example is
Update xxx
Set balance = (Case
.....
End)
Where ......

But I want to do like this:
Update xxx
Set (Case
.....
End) = 1000
Where ......

I have tried this syntax, but it doesn't work. What should I do?
Thanks.

Reply to this Comment

@Kenny,

Are you trying to dynamically select the actual column to update? Could this be something that would be done more effectively using the parent language (ColdFusion, ASP, etc):

UPDATE xxx
<cfif A>
SET a = 1000
<cfelse>
SET b = 1000
</cfif>
WHERE....

Is that what you're trying to do?

Reply to this Comment

Yes, that's what I am trying to do. I have many stored procedures update the same table but different fields, and I am thinking to merge them into one sproc. By doing so, the front end developer can just call the same sproc and do several update processes by varying the keywords.

Reply to this Comment

@Kenny,

You can probably build a dynamic SQL statement and then EXEC() it or something. I've never done this personally, but I think this is how a lot of people handle pagination.

Reply to this Comment

I have the following SQL statement that meets all the requirements of what I am trying to do…

SELECT Diff_Adj,
Case
When Diff_Adj < 0
Then '-'+right ('00000000'+convert (varchar(10), convert (int, -Diff_Adj*100)), 9)
Else right ('000000000'+convert (varchar(10), convert (int, Diff_Adj*100)), 10)
End
FROM dbo.Tran405_CC1

…and these are the results:

Diff_Adj
0000000100
0000000000
0000000000
-000000100
-008638200

Now, instead of running a SELECT statement, I need to run an UPDATE statement to modify data in my table, here is the Update statement I am using to do so…

UPDATE dbo.Tran405_CC1
SET Diff_Adj =
CASE
WHEN Diff_Adj < 0
THEN '-'+right ('00000000'+convert (varchar(10), convert (int, -Diff_Adj*100)), 9)
ELSE right ('000000000'+convert (varchar(10), convert (int, Diff_Adj*100)), 10)
END

…and these are my results:

Diff_Adj
1.00
.00
.00
-1.00
-86382.00

FYI…the data type for Diff_Adj column is Decimal (10, 2)

Thanks in advance

Reply to this Comment

CORRECTED CODE

I have the following SQL statement that meets all the requirements of what I am trying to do…

SELECT Diff_Adj,
Case
When Diff_Adj < 0
Then '-'+right ('00000000'+convert (varchar(10), convert (int, -Diff_Adj*100)), 9)
Else right ('000000000'+convert (varchar(10), convert (int, Diff_Adj*100)), 10)
End
FROM dbo.Tran405_CC1

…and these are the results:

Diff_Adj
0000000001
0000000000
0000000000
-000000001
-000086382

Now, instead of running a SELECT statement, I need to run an UPDATE statement to modify data in my table, here is the Update statement I am using to do so…

UPDATE dbo.Tran405_CC1
SET Diff_Adj =
CASE
WHEN Diff_Adj < 0
THEN '-'+right ('00000000'+convert (varchar(10), convert (int, -Diff_Adj*100)), 9)
ELSE right ('000000000'+convert (varchar(10), convert (int, Diff_Adj*100)), 10)
END

…and these are my results:

Diff_Adj
1.00
.00
.00
-1.00
-86382.00

FYI…the data type for Diff_Adj column is Decimal (10, 2)

Thanks in advance

Reply to this Comment

Brilliant, I was getting some syntax errors when using case during an update statement, but having seen this, the error is now corrected.

Good job.

Reply to this Comment

hello ben,
i want to use the case statment in the Storeprocedure like this can u tell me it is a urgent

example

create proc Testing
(
@jobnumber nvarchar (100),
@flag int
)
begin

update tablename
jobnumber=(case when @flag = 0 than @jobnumber )
end

I have used like this but it showing syntex error so please tell the actual case statment for the storeprocedure ! it is the urgent

Reply to this Comment

CREATE PROC Testing
(
@jobnumber NVARCHAR (100),
@flag INT
)
AS
BEGIN
UPDATE tablename
SET jobnumber=CASE WHEN @flag = 0 THEN @jobnumber ELSE jobnumber END
END

Reply to this Comment

hi Pinal,

I have already use like this but when i use END of case statment than it take the END for the BEGIN not for the case statment.

but anyways maybe i have use some another line there. i will try to use this.

please make comment on my this comment

Reply to this Comment

What about if i only want to update on true ignoring the else?

CASE WHEN 1>0 THEN
UPDATE table field='true' WHERE field='false'
END;

Reply to this Comment

@Alqin,

I am not sure I understand. If you know the value before the UPDATE statement, then you can always just choose not to run the query at all?

Reply to this Comment

@Ben,
I'm playing around with a custom autoincrement.
For example if I delete all entries from mammal category, and there were like 5, when I insert new row the increment has to start from 6.
So...
To remember the last incremented value, I thought that something like DELETE only if there is at least one more entry in the mammal category otherwise update by making null the output data, then on new entry start with an update instead of insert.

Now the real question here is:
I'm using php. If i use the function mysql_query($query) one time to count() if there is one more entry then another time for deleting or updating depending on count(), if other users are updating the database doesn't the incremented value gets messed up?

I used $mysqli->multi_query($query) to execute multiple mysql queries in one $query then i discover that i cant use if() or case() to chose to update or delete the database. So this force my to use at least two php $query calls.

Now I'm curios about the answer on the real question...

Sorry for the long message...

Reply to this Comment

@Alqin,

I don't quite follow your auto-increment stuff, but I think I understand your primary question. When I moved from SQL Server to MySQL, this is the first big thing that stumped me. I used to have conditions around queries all the time. Now that I know what you mean, I can totally relate. I used to do things like this:

SET @id = SELECT....

IF (@id IS NOT NULL) THEN
.... UPDATE
ELSE
.... INESRT
END IF

When I switched to MySQL, I had to break apart all kinds of queries. You can still run multiple queries in a MySQL environment; but, you can't conditionally execute individual queries.

I don't have great advice on this. The best thing I can say is that you might want to wrap the multiple queries in an exclusive-lock. This way, you won't get any dirty reads.

Sorry I couldn't be more helpful.

Reply to this Comment

What you wrote is fine.
Tell me how will you write a sql where you have to update one field only if its null where as other fields should be updated at the same time with no condition.

UPDATE TABLE
SET

A (only if its null)= B.a
,X= B.x
,Y = B.y

Reply to this Comment

@ABHAY,

NEVERMIND I GOT IT, btw i am using MERGE i hope the same logic can be applied for simple update aswell.

UPDATE SET

RTRMNT_FG =
CASE
WHEN TARGET.RTRMNT_FG IS NULL THEN SOURCE.RTRMNT_FG
--UPDATE ONLY WHEN ITS NULL
END
,
DT_OF_RTRMNT = SOURCE.DT_OF_RTRMNT
,
UPD_TS = GETDATE()

thanks guys.

Reply to this Comment

@Abhay,

Are you sure that will always work? Since your CASE statement only has one condition, I am afraid that it will return NULL when that condition is not met.

Reply to this Comment

@ABHAY,

Ben is absolutely correct. You should use following code.

UPDATE SET

RTRMNT_FG =
ISNULL(TARGET.RTRMNT_FG,SOURCE.RTRMNT_FG)
--UPDATE ONLY WHEN ITS NULL
,
DT_OF_RTRMNT = SOURCE.DT_OF_RTRMNT
,
UPD_TS = GETDATE()

Reply to this Comment

@Pinal,

Ah, awesome stuff. I was thinking about using a WHEN/ELSE suggestiong; but the ISNULL() is much more to the point!

Reply to this Comment

@Ben- Thanks for posting this..

@Pinal,

I am wondering whether it has any performance issues or whether IF is a better choice here.

This is what i did....

UPDATE MyTable
SET MyColumn=CASE WHEN @MyVariable IS NOT NULL THEN @MyVariable ELSE MyColumn END
WHERE
<some condition>

Will be good if i get a (CASE Vs IF) interms of Performance. Any Idea??.

Thanks,
Ananthan

Reply to this Comment

Hi... I am trying an Update statement with multiple conditions using where/ case statements. Unable to do it... please help

My Code.......

Update Employee

set age= Null where age < 18
set salary = 800.25 where salary != 1000.25;

I want to set the age to Null if it is less than 18
Also I want to set the min. salary to 800.25 if their mentioned salary is not 1000.25

Reply to this Comment

@sreekanth,

Update YourTable SET age=(CASE WHEN CAST(age AS INT) < 18 THEN NULL ELSE age END), salary=(CASE WHEN CAST(salary AS numeric(18,2)) <> 1000.25 THEN 800.25 ELSE salary END)

Thanks
Manish

Reply to this Comment

Hi Ben
I'm new to your site.
Currently i'm working on TeraData .
Is it okay If I post my queries related to SQL TeraData Extension ?

Thank You !

Reply to this Comment

hi all,

just a doubt,
is it possible to do this?

.......
case
when @TableNameT = 'tbl1' then
update
tbl1a

set [FirstName] = @FirstName,
[SurName] = @SurName,
[Address] = @Address,
[Email] = @Email,
[TelNo] = @TelNo,
[Mobile] = @Mobile

where
POLICY_NO = @PolicyNo
when @TableNameT = 'tbl2'
update
tbl2b
...................

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.