Update Query If Statement for Oracle

Update query if statement for Oracle

You could use CASE expression in the SET clause.

For example,

UPDATE table
SET schema.column =  CASE
                        WHEN CO= 'Y' AND COM='Y' THEN
                          'Y'
                        ELSE
                          'N'
                     END

how to use an if statement in update query in Oracle

Seems you want to use

UPDATE tblname 
   SET "DESC" = CASE 
                WHEN SUBSTR(CODE,1,2)='01' THEN 
                     'OK' 
                WHEN SUBSTR(CODE,1,4)='0001' THEN 
                     SUBSTR(CODE,1,4)
                ELSE
                     'NOT OK'
                END

desc is a reserved keyword. So, there cannot be a column name unquoted.

PL/SQL using IF statement inside Update

Best way I know of doing this is using a case statement as per the example below. Code is untested but should be enough for you to go ahead on.

begin
    for C2 in cursor_sal loop
        update emp
        set name = case
                     when something     then 'George'
                     when somethingelse then 'something2'
                     else 'somthing 3'
                   end
        where current of C2
    end loop;
end;

Oracle conditional update query

Try:

UPDATE emp
   SET job = (CASE empno
                 WHEN 7788
                 THEN 'MANAGER'
                 WHEN 7902
                 THEN 'MANAGER'
                 ELSE NULL
               END)
 WHERE deptno = 20;

Or

UPDATE emp
   SET job = (CASE
                 WHEN empno IN (7788, 7902)
                 THEN 'MANAGER'
                 ELSE NULL
               END)
 WHERE deptno = 20;

Hope this is what you are after...

EDIT: After your comments about the input coming from a number table type something like this should work:

CREATE TYPE number_tab
AS TABLE OF NUMBER
/

Type Created.

CREATE OR REPLACE
PROCEDURE upd_emp (
   p_deptno    IN emp.deptno%TYPE,
   p_empno_tab IN number_tab
)
IS
BEGIN
   UPDATE emp e
      SET e.job = (SELECT (CASE 
                             WHEN t.column_value IS NULL
                             THEN NULL
                             ELSE 'MANAGER'
                            END)
                     FROM TABLE(p_empno_tab) t
                    WHERE t.column_value(+) = e.empno)
    WHERE deptno = p_deptno;
EXCEPTION
   WHEN others
   THEN
      ...Exception handling code
END upd_emp;
/

Conditional update statement in Oracle

Try the following code

UPDATE SAMPLE_TAB1 t 
    SET t.sample_column1 = (
      case when ((SELECT sample_column2 FROM SAMPLE_TAB2 
                          WHERE sample_column2= sample_value2 
                          AND sample_column3  = sample_value3 ) = 'FALSE' )
            then 0
      else
          (SELECT sample_column1 FROM SAMPLE_TAB3 
                         WHERE sample_column4= sample_value4
                         AND sample_column5  = sample_value5 )
      end

    )
  WHERE t.sample_column1 is not null;

Oracle Pl SQL, conditional in UPDATE statement

Here is a proposition of query that can do the job:

UPDATE some_table
SET COLUMN_1 = NVL(I_COLUMN_1, COLUMN_1)
   ,COLUMN_2 = NVL(I_COLUMN_2, COLUMN_2)
WHERE SOME_KEY = I_SOME_KEY

I'm not very familiar with Oracle but in T-SQL i would use ISNULL() function to do the job and the equivalent in Oracle is the NVL() function.

Hope this will help.

PL/SQL Update query with where condition as a select query with some null values

I understand that, in Cursor t_lcpc you should select only those where phone_number is not null.

That would do the trick and you will get only those who have phone_number filled in.

And just curious that why are you using procedure, when you can do that with simple update statement !

---

Related Topics