Skip to Main Content
  • Questions

  • Best method to insert/update a table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 23, 2003 - 10:21 am UTC

Last updated: May 28, 2003 - 7:46 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
Thanks for taking my question and your help in the past .
I have a fact tables which has 3 to 4 million records . I need to refresh this table daily from flat file which has data for most of the columns . I need to fill in other columns with data from two
lookup tables with 40,000 and 25,000 records respectively .
I'm thinking about the following solutions ..
1) Load flat file into temporary table , loop through all records in a
procedure to fill in columns from lookup tables and insert into
target table .
2) Do sql*loader direct path load into target table , and upadte
it with a procedure .

I'm sure you know a better way to do it fast anf efficient .
I'd really appriciate your advice on this .
Thanks in advance ...

and Tom said...

In 9i, this'll be:

merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno )
/

from start to finish -- no loading (just query the flat file), no update step, no insert step. Just a single statement.


In 8i, I would be tempted to:

a) load and analyze the file into a table with a primary key (the merge key).

b) update ( select <columns to be updated>, <columns to update with>
from table_to_be_updated, table_with_updates
where <join by that primary key> )
set <columns to be updated> = <columns to update with>

c) insert into table_to_be_updated
select * from table_with_updates where <primary key> not in (select primary key from table_to_be_updated )



making sure of course that table to be updated had good stats as well (cbo will be mandatory if you want this to run above a snails pace - especially for that not in where we want a big old hash anti join)

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sam, May 23, 2003 - 1:42 pm UTC

Hi Tom,
In 8i, why not just load all the records from the file into the main table. The discarded records will be in a bad file. Load this bad file again into a staging table and do update (select.....)
NOTE: This is the solution in expert one on one.

Tom Kyte
May 24, 2003 - 9:40 am UTC

yes, that is another viable option definitely.

One more Question

A reader, May 23, 2003 - 2:53 pm UTC

Tom,
Thanks for your update . One question though , the primary key in target table is not the primary key in lookup table . Our data looks something like this ..

Target table "Orders"
ORDER_NO ITEM_NO ITEM_NAME ITEM_PRICE
1 101
2 101
3 111
4 111

Lookup table "Items"
ITEM_NO ITEM_NAME ITEM_PRICE
101 CAMERA 300.00
111 DVD 12.00
That's how table "Orders" looks like , after load from file . Ineed to fill it from lookup tables . Same ITEM_NO appers many times in target table can I take advantage of bulk update/insert . Apologize if that doesn't make any sense , I never used bulk so far .
Thanks in advance


Tom Kyte
May 24, 2003 - 9:52 am UTC

it should not be that the primary key in target is the same -- in fact, TARGET need not have a primary key at all -- only lookup does.


you join to the fkey in target and you join that fkey in target to the pk in source (lookup)


you would:

update ( select a.item_name a_item_name, a.item_price a_item_price,
b.item_name b_item_name, b.item_price b_item_price
from orders A, items B
where a.item_no = b.item_no )
set a_item_name = b_item_name,
a_item_price = b_item_price;


Is this due to lack of keys on the table- Pls explain the fundamentals behind this error

Raghu, May 28, 2003 - 2:14 pm UTC 

What is a non key preserved table ? 

CREATE TABLE ORDERS
(ORDER_NO NUMBER(1),
 ITEM_NO  NUMBER(3),
ITEM_NAME VARCHAR2(60),
ITEM_PRICE NUMBER(10));
/


CREATE TABLE ITEMS
(ITEM_NO NUMBER(3),
 ITEM_NAME VARCHAR2(60),
ITEM_PRICE NUMBER (10));
/

INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES (1,101);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(2,101);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(3,111);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(4,101);
/

INSERT INTO ITEMS(ITEM_NO,ITEM_NAME,ITEM_PRICE) VALUES (101,'CAMERA',300);
INSERT INTO ITEMS(ITEM_NO,ITEM_NAME,ITEM_PRICE) VALUES (111,'DVD',12);
/


update ( select a.item_name a_item_name, a.item_price a_item_price,
                b.item_name b_item_name, b.item_price b_item_price
           from orders A, items B
          where a.item_no = b.item_no )
  set a_item_name = b_item_name,
      a_item_price = b_item_price;



SQL> update ( select a.item_name a_item_name, a.item_price a_item_price,
  2                  b.item_name b_item_name, b.item_price b_item_price
  3             from orders A, items B
  4            where a.item_no = b.item_no )
  5    set a_item_name = b_item_name,
  6        a_item_price = b_item_price
  7  /
  set a_item_name = b_item_name,
      *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ORA-01779 cannot modify a column which maps to a non key-preserved table

Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. 

Action: Modify the underlying base tables directly.

Tom Kyte
May 28, 2003 - 7:46 pm UTC

that is saying

"look, you are trying to update ORDERS by joining to ITEMS -- but the problem is a single row in ORDERS could possibly join to 0, 1 (those are OK) or more (<<== thats the problem) rows in ITEMS. Hence ORDERS is not "key preserved" -- a single row in orders could be updated 500 times in that single update statement. Since we cannot assure you of the order of rows being updated -- what that means is an update of a set of data on database 1 could come to a different answer then an update of the SAME EXACT DATA on database 2 (or even worse, running the same update on database 1, rolling back and doing the update later against the same data could come to a different answer"

In order for that update to fly -- ITEM_NO must be known to be unique in ITEMS so that it is 100% assured that each row in ORDERS joins to AT MOST one row in ITEMS.

that is why your example below doesn't work -- the primary key of items must be ITEM_NO by itself in order for this to work -- and if item_no ISN'T unique -- there is no way you can update orders from items, what work in items would be/should be used???

ORA-01779

Raghu, May 28, 2003 - 2:20 pm UTC 

Additon of primary keys did not fix the problem ora-01779


CREATE TABLE ORDERS
(ORDER_NO NUMBER(1),
 ITEM_NO  NUMBER(3),
ITEM_NAME VARCHAR2(60),
ITEM_PRICE NUMBER(10));
/


CREATE TABLE ITEMS
(ITEM_NO NUMBER(3),
 ITEM_NAME VARCHAR2(60),
ITEM_PRICE NUMBER (10));
/

INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES (1,101);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(2,101);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(3,111);
INSERT INTO ORDERS (ORDER_NO,ITEM_NO) VALUES(4,101);
/

INSERT INTO ITEMS(ITEM_NO,ITEM_NAME,ITEM_PRICE) VALUES (101,'CAMERA',300);
INSERT INTO ITEMS(ITEM_NO,ITEM_NAME,ITEM_PRICE) VALUES (111,'DVD',12);
/


update ( select a.item_name a_item_name, a.item_price a_item_price,
                b.item_name b_item_name, b.item_price b_item_price
           from orders A, items B
          where a.item_no = b.item_no )
  set a_item_name = b_item_name,
      a_item_price = b_item_price;



SQL> update ( select a.item_name a_item_name, a.item_price a_item_price,
  2                  b.item_name b_item_name, b.item_price b_item_price
  3             from orders A, items B
  4            where a.item_no = b.item_no )
  5    set a_item_name = b_item_name,
  6        a_item_price = b_item_price
  7  /
  set a_item_name = b_item_name,
      *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table


ORA-01779 cannot modify a column which maps to a non key-preserved table

Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. 

Action: Modify the underlying base tables directly.


SQL>  alter table orders add  constraint o_pk primary key(Order_no);

Table altered.

SQL> update ( select a.item_name a_item_name, a.item_price a_item_price,
  2                  b.item_name b_item_name, b.item_price b_item_price
  3             from orders A, items B
  4            where a.item_no = b.item_no )
  5    set a_item_name = b_item_name,
  6        a_item_price = b_item_price  ;
  set a_item_name = b_item_name,
      *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table



SQL> alter table items add constraint i_pk primary key(item_no,item_name);

Table altered.


  1  update ( select a.item_name a_item_name, a.item_price a_item_price,
  2                  b.item_name b_item_name, b.item_price b_item_price
  3             from orders A, items B
  4            where a.item_no = b.item_no )
  5    set a_item_name = b_item_name,
  6*       a_item_price = b_item_price
SQL> /
  set a_item_name = b_item_name,
      *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Additon of primary keys did not fix the problem ora-01779 -

Reader, May 28, 2003 - 6:01 pm UTC

Raghu,
You need to add primary key in items table and orders table on item_no to use update(select....) set.. command

Additon of primary keys did not fix the problem ora-01779 -

Reader, May 28, 2003 - 6:02 pm UTC

Raghu,
You need to add primary key in items table and orders table on item_no to use update(select....) set.. command

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library