Today I went through few post by Ask Tom, and stuck with this fantastic post

Lets first create following tables to show the concept

SQL> CREATE TABLE table_1 ( c_code NUMBER PRIMARY KEY, c_name VARCHAR2(40) );
Table created.

SQL> CREATE TABLE table_2 ( c_code NUMMBER PRIMARY KEY, c_name VARCHAR2(40) );
Table created.

Note:
We need a PRIMARY KEY or an UNIQUE KEY on source table atleast from where we will be getting the values because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambigous situation.

SQL> INSERT INTO table_1 VALUES ( 1, ‘First Row’ );
SQL> INSERT INTO table_1 VALUES ( 2, ‘Nothing’ );
SQL> INSERT INTO table_2 VALUES ( 2, ‘Second Row’ );

SQL> UPDATE ( SELECT a.c_name a_col, b.c_name b_col
2 FROM table_1 a, table_2 b
3 WHERE a.c_code = b.c_code )
4 SET a_col = b_col
5 /

1 row updated.

SQL> select * from a
2 /

c_code c_name
————– ————————-
1 First Row
2 Second Row

Below are few more variance sql for same purpose, but SQL above gives best chance to optimizer in getting a good execution plan.

SQL> UPDATE table_1 a
2 SET c_name = ( SELECT c_name
3 FROM table_2 b
4 WHERE b.c_code = a.c_code)
5 WHERE EXISTS ( SELECT c_name
6 FROM table_2 b
7 WHERE b.c_code = a.c_code )
8 /

1 row updated.

SQL> UPDATE table_1 a
2 SET c_name = ( SELECT c_name
3 FROM table_2 b
4 WHERE b.c_code = a.c_code )
5 WHERE a.c_code IN ( SELECT c_code
6 FROM table_2 )
7 /

1 row updated.