Update a table from selecting another table

I would like to update emp table by selecting data from a source table temp0430.

I use this SQL statement:

update emp x
set sal = (select sal from temp0430 y where y.empno=x.empno)
where empno in (select empno from temp0430);

Is this correct? Is there any better way?

SQL> select * from emp

EMPNO ENAME SAL
---------- ---------- -----
7369 SMITH 8888
7499 ALLEN 8888
7521 WARD 8888
7566 JONES 8888
7654 MARTIN 8888
7698 BLAKE 8888
7782 CLARK 8888
7788 SCOTT 8888
7839 KING 8888
7844 TURNER 8888
7876 ADAMS 8888
7900 JAMES 8888
7902 FORD 8888
7934 MILLER 8888

14 rows selected.

SQL> select * from temp0430

EMPNO SAL
---------- ----------
7566 2975
7698 2850
7788 3000
7839 5000
7902 3000

SQL> update emp x
2 set sal = (select sal from temp0430 y where y.empno=x.empno)
3 where empno in (select empno from temp0430);

5 rows updated.

SQL> select * from emp;

EMPNO ENAME SAL
---------- ---------- -------
7369 SMITH 8888
7499 ALLEN 8888
7521 WARD 8888
7566 JONES 2975
7654 MARTIN 8888
7698 BLAKE 2850
7782 CLARK 8888
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 8888
7876 ADAMS 8888
7900 JAMES 8888
7902 FORD 3000
7934 MILLER 8888

14 rows selected.

SQL> commit;

Instead of the IN clause, you can:
update emp x
set sal = (select sal from temp0430 y where y.empno=x.empno)
where x.empno = y.empno

Sorry Jeff, but that one won't work. Y is un-defined at the outer level - I simply couldn't resist pointing out your mistake - you make so dang few of them ;).

But now that I've done that, I suppose I should give dba_admin some help :D.

Okay, here are some options - you will probably want to test each one and get the stats - each solution is applicable under different circumstances:

I've changed the table and column names to stuff from my database, and I'm feeling too lazy to change them back and format the statments, so you'll have to do that yourself :)

update org x
set org_dba_nm = (select org_dba_nm from org y where y.org_pk=x.org_pk)
where exists (select 1 from org y where y.org_pk=x.org_pk)

update
(select x.org_pk, x.org_dba_nm, y.org_dba_nm as nm2
from org x, org y
where
y.org_pk=x.org_pk)
set
org_dba_nm = nm2

update org x
set org_dba_nm = (select nvl(y.org_dba_nm, x.org_dba_nm) from org y where y.org_pk(+)=x.org_pk)

...together with your original solution gives you a total of 4 different solutions to the same problem. Each has its own usefulness based on such things as table size, indexes, etc.

HTH,

- Chris

Hi DBA_ADMIN, 2nd May 2001 19:20 hrs chennai

As Chrislong has said to Use EXISTS is a good option with avoids the full table scan.There is by increasing the performance.

Cheers

Padmam

Quote:

---

Originally posted by chrisrlong
Sorry Jeff, but that one won't work. Y is un-defined at the outer level

---

I stand corrected.

That's okay - you're still *the man* :)

- Chris

Thank you all of you!