Home » SQL & PL/SQL » SQL & PL/SQL » Update statement (Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production)
Update statement [message #354845] Tue, 21 October 2008 09:59 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good day.

SQL> select * from emp;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
     7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81      2450                  10
     7788 SCOTT      ANALYST        7566 09-DEC-82      3000                  20
     7839 KING       PRESIDENT           17-NOV-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30
     7876 ADAMS      CLERK          7788 12-JAN-83      1100                  20
     7900 JAMES      CLERK          7698 03-DEC-81       950                  30
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10

14 rows selected.

SQL> select * from deptno;

   DEPTNO DNAME          LOCATION            TOT
--------- -------------- ------------- ---------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON

SQL> select count(*), deptno from emp group by deptno;

 COUNT(*)    DEPTNO
--------- ---------
        3        10
        5        20
        6        30




Is there any way to update using single update statement to update the deptno table tot column, count of deptno from emp table.

Expected result:
   DEPTNO DNAME          LOCATION            TOT
--------- -------------- ------------- ---------
       10 ACCOUNTING     NEW YORK      3   
       20 RESEARCH       DALLAS        5   
       30 SALES          CHICAGO       6
       40 OPERATIONS     BOSTON


Could you please please advice me.

Let em know if you need any more details.

Thanks & Regards
Thangam.
Re: Update statement [message #354846 is a reply to message #354845] Tue, 21 October 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read this and followings:
http://www.orafaq.com/forum/m/353896/102589/?#msg_353896

Regards
Michel
Re: Update statement [message #354860 is a reply to message #354845] Tue, 21 October 2008 12:28 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
iamdurai wrote on Tue, 21 October 2008 10:59

Is there any way to update using single update statement to update the deptno table tot column, count of deptno from emp table.



What happens when you add a new row to EMP? Then you have just lost your data integrity.
Previous Topic: date counting select
Next Topic: Number conversion in oracle
Goto Forum:
  


Current Time: Thu Dec 08 06:30:03 CST 2016

Total time taken to generate the page: 0.07134 seconds