Q

The minimum column value in the row

I want to update FieldC with the minimum value of FieldA and FieldB. Can we do this with only one update query?

Here is my table:

FieldA   FieldB  FieldC
  1        3
  4        2
  7        8
  9        6

I want to update FieldC with the minimum value of FieldA and FieldB. I did it like this:

update mytable 
set FieldC=FieldA where FieldA < FieldB

update mytable set FieldC=FieldB where FieldB < FieldA

Can we do this with only one update query? Thanks.

The answer is yes, but with a caveat.

update mytable 
set FieldC = case when FieldA < FieldB
                  then FieldA
                  else FieldB end

Note that this will actually update FieldC. Your two queries would result in FieldC not being updated when FieldA happens to equal FieldB.

However, there should be no need to do this. You can use that same CASE expression in any query where you would wish to use FieldC. It's usually not a good idea to store data which can be derived like that. Yes, there are exceptions, but this isn't one of them because it's too simple.

Be careful extending this to more than two columns. It may be that you have not normalized your table properly. See my earlier answer Highest value in each row (17 May 2004) for a more complex example.

This was first published in October 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close