Manage Learn to apply best practices and optimize your operations.

How to update columns dynamically

A SearchOracle.com member asks, "How do you update columns dynamically, depending on the conditions?"

How do you update columns dynamically, depending on the conditions?

There are three things to establish: which column, which row, and which value.

Which column is specified in the SET clause. To designate which column(s) to update, you must name them. If the column is updated conditionally, you must still name it.

Which row is specified in the WHERE clause. This can be based on columns other than those being updated.

Which value is specified, if necessary, using a CASE expression.

update salaries
   set bonus =
       case when job = 'dir' then 1000
            when job = 'mgr' then  500
                             else    0 end
     , salary = salary * 
       case when salary < 45000 then 1.02
                                else 1.04 end
 where location = 'hq'

Bonus for directors and managers, plus a 2% increase for everyone under 45,000 and 4% for everyone over. Applies only to employees at home office.

This was last published in January 2008

Dig Deeper on Oracle development languages



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.