Can I use DECODE in a UPDATE statement?

Question

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

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

This is giving me error as eual sign ignored..

Answer 1

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

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

Also, as a bonus, it'll handle the possible situation where the update variable is set to something other than one or two!

Answer 2

No you can't do that. 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 could use dynamic SQL like this:

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

Answer 3

You cant use decode in an update statement. You can however use a merge statement.

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 are using the select portion of the merge to decode col1 and col2 to either the update_value or the value that exists already.

This may also be too verbose for your needs, and the solution that uses an if statement or execute immediate may better suit your problem.