Re: Update Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 22 Nov 1998 17:46:07 GMT
Message-ID: <365e4dbd.17129280@192.86.155.100>

A copy of this was sent to shirushah_at_hotmail.com (if that email address didn't require changing) On Sun, 22 Nov 1998 02:58:59 GMT, you wrote:

>Table Name: Emp
>
>ename sal dnum
>John Chen 1,000 15
>Tom Hanks 2,000 12
>Amy Hall 2,500 15
>Don Fonz 3,000 12
>
>Table Name: Dept
>
>dnum dname tot_sal
>15 Sales
>12 R&D
>14 Marketing
>
>I need to Update DEPT by setting tot_sal equal to the total salary of
>employees in each department. If a department has no employees, tot_sal
>should be set to zero. (I need to use only one sql statement)
>

given the following data:

SQL> select * from emp;

ENAME SAL DEPTNO
---------- ---------- ----------

SMITH             800         20
ALLEN            1600         30
WARD             1250         30
JONES            2975         20
MARTIN           1250         30
BLAKE            2850         30
CLARK            2450         10
SCOTT            3000         20
KING             5000         10
TURNER           1500         30
ADAMS            1100         20
JAMES             950         30
FORD             3000         20
MILLER           1300         10

14 rows selected.

SQL> select * from dept;

DEPTNO DNAME TOT_SAL
---------- -------------- ----------

        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

The update would be:

SQL> update dept
2 set tot_sal = ( select nvl( sum(sal), 0 )

  3                      from emp where emp.deptno = dept.deptno );

4 rows updated.

SQL> select * from dept;

DEPTNO DNAME TOT_SAL
---------- -------------- ----------

        10 ACCOUNTING           8750
        20 RESEARCH            10875
        30 SALES                9400
        40 OPERATIONS              0

SQL>
>So at the end of the update dep should look like this.
>dnum dname tot_sal
>15 Sales 3,500
>12 R&D 5,000
>14 Marketing 0
>
>Thanks
>SS
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 22 1998 - 11:46:07 CST