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
Solved

oracle insert and update

Posted on 2011-09-21
8
569 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 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
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 34

Expert Comment

by:johnsone
ID: 36575912
Too slow.   :)
0
 
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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

856 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