Re: improving this SQL statement

From: Mihail Daskalov <mdaskalo_at_tlogica.com>
Date: 15 Apr 2002 09:32:55 -0700
Message-ID: <9f9e62ec.0204150832.e59584e_at_posting.google.com>


"Jay" <heilongjiang__at_hotmail.com> wrote in message news:<3cba52c3_at_news.starhub.net.sg>...
> Hi,
>
> I have a very similar problem in my project and I simulate the
> situation in SCOTT/TIGER account.
>
> Appreciate if anyone can improve or suggest a better SQL
> than the one below. I somehow feel unsafe about that
> part >>
> WHERE EMPNO IN (SELECT ...);
>
> Is my concern unfounded?
> Is this a corelated UPDATE?
>
> Thank You.
>
> ================================================================
>
>
> 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 19-APR-87 3000
> 20
>
> 7839 KING PRESIDENT 17-NOV-81 5000
> 10
>
> 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
> 30
>
> 7876 ADAMS CLERK 7788 23-MAY-87 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
>
>
>
> SQL> select * from new_sal;
>
>
>
> EMPNO SAL
>
> --------- ---------
>
> 7934 2000
>
> 7900 2100
>
> 7369 2200
>
>
> update emp a
> set a.sal = (select b.sal
> from new_sal b
> where b.empno = a.empno)
> where a.empno in (select empno from new_sal);

Hi you could use:

update (select a.empno,a.sal,b.sal as newsal from emp a, new_sal b where a.empno=b.empno) a set a.sal = a.newsal

after implementing some constraints:

alter table new_sal
add constraint new_sal_fk foreign key(empno) references emp(empno);

alter table new_sal add constraint new_sal_pk primary key (empno);

This should be a little more optimal, but you should test it in you environment.

Otherwise you UPDATE statement seems totally correct corellated update to me.

Regards,
Mihail Daskalov
Brainbench MVP for Oracle Admin Received on Mon Apr 15 2002 - 18:32:55 CEST

Original text of this message