Oracle conditional update query

I have one requirement as shown below :

Suppose we have EMP table with 5 rows having deptno = 20 --Here i will get deptno i.e.20 from input paramter

EMPNO   ENAME   JOB       MGR   HIREDATE    SAL     COMM    DEPTNO 
--------------------------------------------------------------------------------
7566    JONES   MANAGER   7839  04/02/1981  2975    -   20 
7788    SCOTT   ANALYST   7566  12/09/1982  3000    -   20 
7902    FORD    ANALYST   7566  12/03/1981  3000    -   20 
7369    SMITH   CLERK     7902  12/17/1980  800     -   20 
7876    ADAMS   CLERK     7788  01/12/1983  1100    -   20 

Now I want to write one UPDATE query in procedure which will update EMP Table set JOB = 'MANAGER' where empno in (7788,7902); -- Here empno i.e. 7788 and 7902 i will get from input parameter

My update query Should update JOB = 'MANAGER' for 2 rows having deptno = 20 and for rest of 3 rows i want to update will Null values.

So that final output will be as shown below :

EMPNO   ENAME   JOB       MGR   HIREDATE    SAL     COMM    DEPTNO 
--------------------------------------------------------------------------------
7566    JONES             7839  04/02/1981  2975    -   20 
7788    SCOTT   MANAGER   7566  12/09/1982  3000    -   20 
7902    FORD    MANAGER   7566  12/03/1981  3000    -   20 
7369    SMITH             7902  12/17/1980  800     -   20 
7876    ADAMS             7788  01/12/1983  1100    -   20 

So what will be the best way to write this query

Answers


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;
/

Need Your Help

How to iterate over each item in a computed Ember array in a handlebar view

ember.js handlebars.js

I have an controller which is an ArrayProxy and contains some computed arrays.

Do redirect pages need meta tags?

php seo metadata redirect

Just curious if you have say an index page that redirects to another page, when search engines index the site, do they follow the redirect and scan the page it is redirected to? Wondering if I'd ne...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.