asked on

oracle insert and update

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.

Jacobfw

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

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

johnsone

Too slow. :)

slightwv (䄆 Netminder)

This update might be more efficient:

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

Jacobfw

>> 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)

it-rex

ASKER

thanks

Sean Stuber

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)