improving this SQL statement
Date: Mon, 15 Apr 2002 12:11:57 +0800
Message-ID: <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?
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
Is this a corelated UPDATE?
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); Received on Mon Apr 15 2002 - 06:11:57 CEST