Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle - Updating one column or another based on a condition

Tags:

sql

oracle

I want to update a record in a table but based on a condition I will either update one column or another but I do not want to have 2 separate statements because the statements are very long and detailed.

Here is the basic idea with over simplification to get to the point.

PROCEDURE Animal_something(p_updater VARCHAR2)

begin

  if p_updater = 'person' then   
    -- I want to update the modified_by  
  else   
    -- if p_updater = 'a process' I want to update modified_by_process

Update table_creatures
   set animal_type = 'Dog ,

**modified_by** = 'Bob'   
**or do this**  
**modified_by_process =** 'creature_package'

 where animal_legs = '4'

I don't want:

if p_updater = 'person' then 
  Update table_creatures   
     set animal_type = 'Dog ,  
         modified_by = 'Bob'  
   where animal_legs = '4';  
else  

  Update table_creatures  
     set animal_type = 'Dog , 
         modified_by_process = 'creature_package'  
   where animal_legs = '4';

end;
like image 794
z-dan Avatar asked May 24 '10 15:05

z-dan


People also ask

How do you update a column based on a condition?

To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.

How update multiple columns with different values in Oracle?

Introduction to the Oracle UPDATE statement If you update more than two columns, you separate each expression column = value by a comma. The value1 , value2 , or value3 can be literals or a subquery that returns a single value. Note that the UPDATE statement allows you to update as many columns as you want.

How do you update multiple columns in SQL with different conditions?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.

How do you update a table column with another table column in Oracle?

Update Table by selecting rocords from another table. Syntax: UPDATE table1. SET column1 = (SELECT expression1.


1 Answers

UPDATE  table_creatures
SET     animal_type = 'Dog',
        modified_by = CASE p_updater WHEN 'person' THEN 'Bob' ELSE modified_by END,
        modified_by_process = CASE p_updater WHEN 'process' THEN 'creature_package' ELSE modified_by_process END
WHERE   animal_legs = 4
like image 160
Quassnoi Avatar answered Oct 30 '22 17:10

Quassnoi