Solved

oracle insert and update

Posted on 2011-09-21
8
570 Views
Last Modified: 2012-05-12
CREATE TABLE tab1
(
  KEYVAL  NUMBER,
  VAL1    NUMBER,
  VAL2    NUMBER,
  VAL3    NUMBER
)

CREATE TABLE tab2
(
  KEYVAL  NUMBER,
  VAL1    NUMBER,
  VAL2    NUMBER,
  VAL3    NUMBER
)
SET DEFINE OFF;
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (72889, 640, 8700, 728);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (782870, 641, 103467, 782);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (80929, 642, 183471, 809);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (313944, 643, 77189, 313);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (396695, 644, 141851, 396);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (857024, 645, 196052, 857);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (534823, 646, 8272, 534);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (898224, 647, 22097, 898);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (560690, 648, 138116, 560);
Insert into tab1
   (KEYVAL, VAL1, VAL2, VAL3)
 Values
   (385301, 649, 51978, 385);


we need to take the data in the tab1 and
1. insert rows not currently in the tab2 table and
 2. update tab2 rows with a matching KEYVAL value with the data in tab1.

I do not know if plsql will be easier here I think so.
0
Comment
Question by:it-rex
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575886
insert into tab2
select * from tab1
where not exists (select 1
                  from   tab2
                  where  tab2.keyval = tab1.keyval);

update tab2
set val1 = (select val1
            from   tab1
            where  tab1.keyval = tab2.keyval),
   val2 = (select val2
            from   tab1
            where  tab1.keyval = tab2.keyval),
   val3 = (select val3
            from   tab1
            where  tab1.keyval = tab2.keyval);

You could do this more efficient in Pl/SQL, but it will primarly depend on the amount of data you are processing.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36575902
pl/sql is definitely NOT the way to go with this, too inefficient (regadless of the amount of data)

just use merge,  one sql statement to do both operations

merge into tab2 t2
using(select * from tab1) t1
on (t1.keyval = t2.keyval)
when matched then
update set t2.val1 = t1.val1,t2.val2 = t1.val2, t2.val3 = t1.val3
when not matched then
insert (keyval,val1,val2,val3) values (t1.keyval,t1.val1,t1.val2,t1.val3)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 36575909
Look at the MERGE statement.  That sounds like what you are trying to do.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_9016.htm#i2081218
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:johnsone
ID: 36575912
Too slow.   :)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575918
This update might be more efficient:

update tab2
set (val1, val2, val3) = (select val1, val2, val3
            from   tab1
            where  tab1.keyval = tab2.keyval)
;

0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575943
>> This update might be more efficient:

yes, that is a better writen update.

the merge statement is much better and combines the insert and update, introduced in 9i  (thanks for that example)
0
 
LVL 11

Author Closing Comment

by:it-rex
ID: 36575961
thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36575964
that update won't add in the new values.


it does (with the data above) do slightly fewer db block gets (45) vs the merge (54)
but other stats (as measured by autotrace) are virtually identical except consistent gets where the merge
is much better (18) vs the update (82)

but, more importantly,  the update doesn't do an insert, so using it requires 2 sql and hence double io on both tables

use MERGE (but, don't trust me, always test all claims)

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  ā€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question