Can I use DECODE in an UPDATE statement?

Question

Can I use DECODE in an UPDATE statement on the left side of a SET?

UPDATE temp SET DECODE (update_var, 1, col1, 2, col2) = update_value;

This gives me an error as the eual sign is ignored.

Answer 1

How about this? If the update flag is set to 1, col1 is updated, if set to 2, then col2 is updated.

UPDATE temp SET col1 = DECODE(update_var, 1, update_value, col1), col2 = DECODE(update_var, 2, update_value, col2) 

In addition, as a bonus, it will handle a possible situation when the update variable is set to a value other than one or two!

Answer 2

No, you cannot do this. You can do this in PL / SQL:

 IF update_var = 1 THEN UPDATE temp SET col1 = update_value; else UPDATE temp SET col2 = update_value; END IF; 

Or you can use dynamic SQL as follows:

 l_sql := 'UPDATE temp SET col'||update_var||' = :v'; EXECUTE IMMEDIATE l_sql USING update_value; 

Answer 3

You cannot use decoding in update instructions. However, you can use the merge operator.

http://psoug.org/reference/merge.html

 MERGE INTO temp b USING ( SELECT key, DECODE(update_var, 1, update_value, col1) as col1, DECODE(update_var, 2, update_value, col2) as col2 FROM temp WHERE key =theKeyIPassedIn) e ON (b.key = e.key) WHEN MATCHED THEN UPDATE SET b.col1 = e.col1, b.col2 = e.col2 ; 

Basically you use the merge selection part to decode col1 and col2 either update_value or an existing value.

This may be too verbose for your needs, and a solution that uses an if statement or takes an immediate action might better suit your problem.