Home » SQL & PL/SQL » SQL & PL/SQL » What is the difference between these 2 sql statements
What is the difference between these 2 sql statements [message #378400] Tue, 30 December 2008 04:47 Go to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi All,



SQL> ed
Wrote file afiedt.buf

  1  SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
  2          FROM    emp b, Emp a
  3*         WHERE   b.deptno = a.deptno
SQL> /
SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
        *
ERROR at line 1:
ORA-00937: not a single-group group function


SQL> UPDATE  emp a
  2  SET     sal = (
  3          SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
  4          FROM    emp b
  5          WHERE   b.deptno = a.deptno
  6  );

14 rows updated.


I have pasted the 2 sql statments where as 1st sql statement is raising an error by saying a.sal is not a group by column.

  1  SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
  2          FROM    emp b, Emp a
  3*         WHERE   b.deptno = a.deptno
SQL> /
SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
        *
ERROR at line 1:
ORA-00937: not a single-group group function


but the 2nd one is executing without raising an error.

SQL> UPDATE  emp a
  2  SET     sal = (
  3          SELECT  a.sal + trunc(0.04 * avg(b.sal), 2)
  4          FROM    emp b
  5          WHERE   b.deptno = a.deptno
  6  );

14 rows updated.


Kindly clarify this as i am bit confused with this two statements.

Thanks for your help and interest toward this forum.
Re: What is the difference between these 2 sql statements [message #378408 is a reply to message #378400] Tue, 30 December 2008 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no relation between the 2.
Add "group by a.sal" and it will work.

Regards
Michel
Re: What is the difference between these 2 sql statements [message #378539 is a reply to message #378408] Tue, 30 December 2008 22:23 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi Michel,

if we add group by a.sal for the 1st sql statement no doubt it will work.

My question is why group by a.sal is not required in the co-related subquery i.e. 2nd sql statement (Update statement). Why is that update statement is working without group by.

Kindly share your knowledge on this.

Thanks
Regards,
Sathya
Re: What is the difference between these 2 sql statements [message #378544 is a reply to message #378539] Tue, 30 December 2008 22:44 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
sathyam2627 wrote on Wed, 31 December 2008 05:23
My question is why group by a.sal is not required in the co-related subquery i.e. 2nd sql statement (Update statement). Why is that update statement is working without group by.

You already answered yourself: it is because it is correlated to the row in table A, so there isno need for grouping.

Maybe its slight rewrite will show it clearer:
UPDATE  emp a
SET     sal = a.sal + (
        SELECT  trunc(0.04 * avg(b.sal), 2)
        FROM    emp b
        WHERE   b.deptno = a.deptno
);
Previous Topic: Learning
Next Topic: Deleting a large number of rows...
Goto Forum:
  


Current Time: Mon Dec 05 10:40:44 CST 2016

Total time taken to generate the page: 0.06890 seconds