Oracle - conditional single column update

Question

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

Here is the basic idea with 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 do not 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 can use dynamic SQL, for example:

 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: the use of bind variables and it is not necessary to update both columns at each run, which will lead to a repeated change, even if the actual value is not changed.

In the opposite direction, the statement is not checked 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.