Re: Update Question

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 22 Nov 1998 15:35:14 GMT
Message-ID: <365812d3.8075348@news.siol.net>

On Sun, 22 Nov 1998 02:58:59 GMT, shirushah_at_hotmail.com 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)
>
>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

SQL> SELECT * FROM emp;

ENAME                       SAL       DNUM
-------------------- ---------- ----------
John Chen                  1000         15
Tom Hanks                  2000         12
Amy Hall                   2500         15
Don Fonz                   3000         12

SQL> SELECT * FROM dept;

      DNUM DNAME                   TOT_SAL
---------- -------------------- ----------
        15 Sales
        12 R&D
        14 Marketing

SQL> UPDATE dept SET tot_sal =
2 (SELECT NVL(SUM(emp.sal),0) FROM emp 3 WHERE emp.dnum = dept.dnum);

3 rows updated.

SQL> SELECT * FROM dept;

      DNUM DNAME                   TOT_SAL
---------- -------------------- ----------
        15 Sales                      3500
        12 R&D                        5000
        14 Marketing                     0

>Thanks
>SS
>

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Nov 22 1998 - 09:35:14 CST