Oracle - Updating one column or another based on a condition

Question

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;

Answer 1

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

Answer 2

You could use dynamic SQL, e.g.:

PROCEDURE Animal_something(p_updater VARCHAR2)

  sql_string_pt1  VARCHAR2(2000) := 'UPDATE table_creatures SET animal_type = :1';
  sql_string_pt2  VARCHAR2(2000) := NULL;
  sql_string_pt3  VARCHAR2(2000) := ' WHERE animal_legs = :3';

begin

  if p_updater = 'person' then   
    sql_string_pt2 := ', modified_by = :2';
  else
    sql_string_pt2 := ', modified_by_process = :2';
  end if;

  EXECUTE IMMEDIATE sql_string_pt1 || sql_string_pt2 || sql_string_pt3
    USING 'Dog', 'Bob', '4';

end;

This has two advantages over Quassnoi's answer: use of bind variables, and not needing to update both columns on every execution, which would generate redo even though the actual value is not changed.

On the downside, the statement is not validated at all at compile time.

Answer 3

UPDATE  table_creatures 
SET     animal_type = 'Dog', 
        modified_by = DECODE(p_updater , 'person' , 'BOB' , 
                                         'proces' , 'creature_package' ,
                                         'GIVE DEFAULT VALUE')          
WHERE   animal_legs = 4;

You can try this.

Answer 4

.............................