Updating a table from another table

Updating a table from another table

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_col2 FROM table_1 a, table_2 b3 WHERE a.c_code = b.c_code )4 SET a_col = b_col5 /
1 row updated.
SQL> select * from a2 /
c_code c_name————– ————————-1 First Row2 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 a2 SET c_name = ( SELECT c_name3 FROM table_2 b4 WHERE b.c_code = a.c_code)5 WHERE EXISTS ( SELECT c_name6 FROM table_2 b7 WHERE b.c_code = a.c_code )8 /
1 row updated.
SQL> UPDATE table_1 a2 SET c_name = ( SELECT c_name3 FROM table_2 b4 WHERE b.c_code = a.c_code )5 WHERE a.c_code IN ( SELECT c_code6 FROM table_2 )7 /
1 row updated.