Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

oracle insert and update

Posted on 2011-09-21
8
Medium Priority
?
578 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

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

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

572 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