OracleBrains.Com header image 2

Updating a table from another table

September 16th, 2007 by Rajender Singh · 7 Comments

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.


Tags: Interesting Coding Showcase · SQL and PL/SQL

7 responses so far ↓

  • 1 Amit // Sep 18, 2007 at 2:58 pm

    nice solution
    thanks
    Amit

  • 2 Rajender Singh // Sep 18, 2007 at 3:27 pm

    You are welcome, Amit!

  • 3 Regina Young // Oct 27, 2008 at 4:15 am

    I also like this solution. But, I have a question. I have two tables. One is used as a template. When I enter new information in the template table, I need the same information “inserted” into the second table, including the PK. Can I do this?

  • 4 Rajender Singh // Oct 27, 2008 at 3:58 pm

    Please provide more details!

  • 5 Rajender Singh // Oct 27, 2008 at 4:14 pm

    Hi Regina,

    I asked you to provide more details as I am thinking may there is something else which you are trying to ask which is related to the given post.

    At present what you have asked is very simple but out of context of given post.

    You can simply create a row level DML trigger on template table , which can insert the row in the second table.

    Thanks & Regards,

    Rajender

  • 6 Regina Young // Oct 28, 2008 at 3:11 am

    What I am asking is this. I have two tables. The template table holds some information that will be needed in the second table. When I create a new record in the template table, I need the same information, same record to also go into the second table. Is there a way I can do this? I hope this makes more sense. Thanks.

  • 7 Rajender Singh // Oct 28, 2008 at 1:18 pm

    You can simply create a row level database trigger on template table , which can insert the row in the second table whenever any DML is issued against it.

    Rajender

Leave a Comment