Case statement in update statement

oracleoracle-sql-developer

I am trying to update a table. I want to use a case statement to decide what column to update. I keep getting the error message, "syntax error at or near "case"." Can someone please help me find this syntax error. I have been looking all morning:

CREATE OR REPLACE FUNCTION UPDATE_PERSON(TARGET_COL INT, UPDATED_VALUE VARCHAR, TARGET_ID CHARACTER)
RETURNS VOID
AS $$ 
    UPDATE PERSON
    SET
    CASE TARGET_COL
        WHEN 1 THEN FIRST_NAME = UPDATED_VALUE WHERE PERSON_ID = TARGET_ID
        WHEN 2 THEN LAST_NAME = UPDATED_VALUE WHERE PERSON_ID = TARGET_ID
        ELSE FIRST_NAME = UPDATED_VALUE WHERE PERSON_ID = TARGET_ID
    END;    
$$ LANGUAGE SQL;

Best Answer

A CASE expression can only return a value, not a fragment of a query.

In order to parametrise which column should receive the value passed as an argument, you could try this approach:

UPDATE
  PERSON
SET
  FIRST_NAME = CASE WHEN TARGET_COL = 2 THEN FIRST_NAME    ELSE UPDATED_VALUE END,
  LAST_NAME  = CASE WHEN TARGET_COL = 2 THEN UPDATED_VALUE ELSE LAST_NAME     END
WHERE
  PERSON_ID = TARGET_ID
;

I slightly simplified the logic, because according to your CASE expression, you want to update FIRST_NAME both when TARGET_COL is 1 and when it is anything other than 2. But the second condition actually covers the first one. So we can simplify the logic to this: if TARGET_COL is two, you want to update LAST_NAME, otherwise you want to update FIRST_NAME.

So how does the above query work? If TARGET_COL is 2, then FIRST_NAME is updated to FIRST_NAME, i.e. to the same value it already has. This way it remains unchanged. In contrast, LAST_NAME in this case is set to UPDATED_VALUE. If, however, TARGET_COL is not 2 (but e.g. 1), then FIRST_NAME gets UPDATED_VALUE, while LAST_NAME is set to LAST_NAME – in other words, remains unchanged.

Related Question