Solved

oracle insert and update

Posted on 2011-09-21
8
567 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 73

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 34

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
 
LVL 34

Expert Comment

by:johnsone
ID: 36575912
Too slow.   :)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 66
levels for reporting 5 63
Migrating an SQL 2008 database to Oracle 12c 3 88
ORA-12560: TNS:protocol adapter error 8 80
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now