How to update with a case statement in oracle?

2.3K Asked by ClaudineTippins in SQL Server , Asked on Mar 16, 2023

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;

Answered by Diya tomar

To update with case statement in oracle, you must understand that - 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.

Your Answer

Answer (1)

Ranjana

In Oracle SQL, you can use the CASE statement within an UPDATE statement to conditionally update rows based on specific criteria. Here's a basic syntax example:

UPDATE table_name

SET column_name = 

    CASE

        WHEN condition1 THEN value1

        WHEN condition2 THEN value2

        ...

        ELSE default_value

    END

WHERE condition;

Let's break down the components:

table_name: The name of the table you want to update.

column_name: The name of the column you want to update.

condition1, condition2, etc.: Conditions that evaluate to either true or false. These conditions determine which rows will be updated.

value1, value2, etc.: The values to set for the column if the corresponding condition is true.

default_value: The value to set if none of the conditions are true.

WHERE condition: An optional condition that further filters the rows to be updated. If omitted, all rows in the table will be updated.

Here's a concrete example:

Let's say we have a table called employees with columns salary and department. We want to give a 10% salary increase to employees in the "IT" department and a 5% increase to employees in the "HR" department.

UPDATE employees

SET salary = 

    CASE

        WHEN department = 'IT' THEN salary * 1.1

        WHEN department = 'HR' THEN salary * 1.05

        ELSE salary

    END

WHERE department IN ('IT', 'HR');

In this example:

If an employee is in the "IT" department, their salary is increased by 10%.
If an employee is in the "HR" department, their salary is increased by 5%.
Employees in other departments remain unchanged.

Always ensure your UPDATE statements are well-tested and verified before running them, especially if they affect a large number of rows or critical data.

6 Months