Oracle - update join - non key-preserved table

Question

I'm trying to replicate an Ingres "update tbl1 from tbl2" command, which doesn't exactly exist in Oracle.

So I use "update (select tbl1 join tbl2...)" command. Both tables have primary keys defined and I thought that my join was uniquely identifying rows, but I'm still getting "ORA-01779: cannot modify a column which maps to a non key-preserved table".

Here are suitably anonymized table definitions and the update I'm trying to execute:

CREATE TABLE tbl1
(
   ID decimal(11) NOT NULL,
   A varchar2(3) NOT NULL,
   B float(7),
   CONSTRAINT tbl1_pk PRIMARY KEY (ID,A)
)
;

CREATE TABLE tbl2
(
   ID decimal(11) NOT NULL,
   A varchar2(3) NOT NULL,
   B float(15),
   C float(15),
   D char(1) NOT NULL,
   CONSTRAINT tbl2_PK PRIMARY KEY (ID,A,D)
)
;

UPDATE 
  (select tbl1.b, tbl2.c 
   from tbl1 inner join tbl2 
   on tbl1.id=tbl2.id 
   and tbl1.a=tbl2.a 
   and tbl1.b=tbl2.b 
   and tbl1.a='foo' 
   and tbl2.D='a') 
set b=c;

How can I define my select such that Oracle will be satisfied that I have no uniqueness violations?

score 29 · Accepted Answer · answered Feb 17 '12 at 21:27

You should be able to do this with a correlated subquery

UPDATE tbl1 t1
   SET t1.b = (SELECT c
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')
 WHERE t1.a = 'foo'
   AND EXISTS( SELECT 1
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')

The problem with the UPDATE that you've written is that Oracle cannot guarantee that there is exactly 1 tbl2.c value that corresponds to a single tbl1.b value. If there are multiple rows in tbl2 for any particular row in tbl1, the correlated update is going to throw an error indicating that a single-row subquery returned multiple rows. In that case, you'd need to add some logic to the subquery to specify which row from tbl2 to use in that case.

This appears to solve the problem -- and thanks for the further explanation. It makes sense. — Jim Kiley, Feb 21 '12 at 16:06

score 1 · Answer 2 · edited Apr 15 '17 at 09:41

1

This statement fails with an error (ORA-01779 cannot modify a column which maps to a non key-preserved table), because it attempts to modify the base tbl1table, and the tbl1 table is not key-preserved in the view . because although (ID,A) is a key of the dept table, it is not a key of the join.

edited Apr 15 '17 at 09:41

Bhargav Rao

50,140
28
121
140

answered Apr 23 '13 at 06:46

王奕然

3,891
6
41
62

The link does not exist anymore, could you please update or summarize the information from the correct page? – Kosi2801 Nov 03 '14 at 13:33
It still exists, but now it is http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11783. It refers to : Oracle 11.2 Database Administrator's Guide -> § 24 Managing Views, Sequences, and Synonyms -> §§ Key-Preserved Tables – user1136452 Mar 17 '16 at 11:38

score 0 · Answer 3 · answered Aug 12 '14 at 11:38

0

It seems your view is not a key-preserved view according to (http://www.orafaq.com/tuningguide/updateable%20view.html). Indeed you make your join on not primary key which seems to be not allowed.

answered Aug 12 '14 at 11:38

Abbadon

2,513
3
25
33

Oracle - update join - non key-preserved table

3 Answers3

Linked

Related