How to update with a case statement in oracle?

Question

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;

Claudine Tippins · Accepted Answer

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.

Ranjana Admin · Answer

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.

How to update with a case statement in oracle?

About this question

Your answer

2 Answers

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!