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;
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.
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.
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.
Update Table by selecting rocords from another table. Syntax: UPDATE table1. SET column1 = (SELECT expression1.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With