?
Solved

oracle insert and update

Posted on 2011-09-21
8
Medium Priority
?
573 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

777 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