ORA-00904 invalid identifier while update table using CASE statement

Question

I create a stored procedure in which I update a table using a CASE statement. user_in is the input parameter of the stored procedure.

Here is the UPDATE statement:

update tbl
set col1 = CASE WHEN (user_in = txt.col3) THEN 'ABC'
                ELSE 'XYZ'
           END
where col2 = v_col2;

Where user_in and v_col2 are input parameters, txt is the another table where we are matching the value of col3 to user_in value. If they match, then set col1 of tbl to ABC, otherwise set it to XYZ.

While executing the stored procedure, I get an error:

ORA-00904 invalid identifier

How to resolve this issue so that i can easily update the table and the stored procedure will compile successfully. Thanks

Answer 1

You may create it in such a way :

create or replace procedure pr_upd_tbl( v_col2 int, user_in int ) is
begin
update tbl t
   set col1 = CASE
                WHEN (user_in = ( select col3 from txt x where x.id = t.id ) ) THEN
                 'ABC'
                ELSE
                 'XYZ'
              END
 where col2 = v_col2;
end;