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.
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.