How to get rid of ORA 01779 error :key preserved table

Question

Here is the sql statement :

UPDATE 
(SELECT table1.nbqe as OLD_nbqe, table2.nbqe as NEW_nbqe,
  table1.adr1 as OLD_adr1, table2.adr1 as NEW_adr1
  table1.adr3 as OLD_adr3, table2.adr2 as NEW_adr3
 FROM table1
 INNER JOIN table2
 ON table1.cg= table2.cg AND table1.ce = table2.ce
) t
SET t.OLD_nbqe = t.NEW_nbqe, t.OLD_adr1 = t.NEW_adr1, t.OLD_adr3 = t.NEW_adr3

This occurs a 01779 error : cannot modify a column which maps to a non key-preserved table

How can I modify the sql statement to realize the operation ?

Ty

Note : this question is no duplicate

table1 columns :
 nbqe
 adr1
 adr2
 adr3
 cg
 ce

table2 column :
 nbqe
 adr1
 adr2
 cg
 ce

in table 2 , cg + ce is a single key

in table 1 you can have several records with the same couple (cg, ce).

no constraints of these tables, not even primary keys or anything.

I will ask it differently. The sql statement is probably wrong.

The select inside the update returns the 8 rows of table 1 that should be modified with the value of table2 that can be found in the t table.

How do I update the 8 row sof table1 with the corresponding values of table2 using the table t : is that possible or should I write a big sql statement repeating all the time the same sub query which seems to very strange and not clean.

The syntax of your update is not correct, please share the table structure and the column you want to update with which values. — Cyrille MODIANO, Sep 21 '17 at 09:57
Actually it is a duplicate, say of [this SO thread](https://stackoverflow.com/q/9335536/146325) because the solution to ORA-01779 is standard. You need to fix the inner join so you get one row from `table2` for each row in `table1` or use correlated subqueries instead. However I will give you a chance to explain why it's not a duplicate before closing it. — APC, Sep 21 '17 at 10:04
Note that we cannot fix the query for you. This error is due to the constraints on your tables and how the business rules apply to the data. It is **your data model**. You understand the business rules, you can see the data. So you have the necessary information and we don't. — APC, Sep 21 '17 at 10:07
"should I write a big sql statement repeating all the time the same sub query which seems to very strange and not clean". Said the developer with tables that have no primary keys or constraints of any kind. That, I am afraid, is your problem right there. — APC, Sep 21 '17 at 10:25
@APC, you probably dont work in computers so you do not know but let me tell you that sometimes you get old programms that are not very clean and you have to do with that..it does not mean you have to continue to work that way. — mlwacosmos, Sep 21 '17 at 10:29
The point is, it's unreasonable to expect SQL to work against data which doesn't conform to basic relational principles. Obviously we all work with legacy systems from time to time, and that frequently means we write ugly code. We just have to remember that the ugliness is due to the data structures not the language. — APC, Sep 21 '17 at 10:34
Does this answer your question? [ORA-01779: cannot modify a column which maps to a non key-preserved table](https://stackoverflow.com/questions/17092560/ora-01779-cannot-modify-a-column-which-maps-to-a-non-key-preserved-table) — Vadzim, May 12 '20 at 18:59

score 4 · Accepted Answer · answered Sep 21 '17 at 10:55

If there is no more than one row in table2 corresponding to table1 then use merge:

merge into table1 d
using table2 s
on (d.cg = s.cg and d.ce = s.ce)
when matched then update set
    d.nbqe = s.nbqe, d.adr1 = s.adr1, d.adr3 = s.adr3

Only matching rows will be updated, rest remains untouched. You could do it with update, but this way You avoid repeating condition for where clause.

Test:

create table table1 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);
create table table2 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);

insert into table1 values (1, 1, 1, 1, 1, 1);
insert into table1 values (2, 2, 2, 2, 2, 2);
insert into table1 values (3, 3, 3, 3, 2, 2);

insert into table2 values (5, 5, 5, 5, 2, 2);

Result:

NBQE ADR1 ADR2 ADR3   CG   CE
---- ---- ---- ---- ---- ----
   1    1    1    1    1    1
   5    5    2    5    2    2
   5    5    3    5    2    2

Thank you very much, this is perfect. – mlwacosmos Sep 21 '17 at 11:11 — mlwacosmos, Sep 21 '17 at 11:11

Cyrille MODIANO · Answer 2 · 2017-09-21T10:28:59.827

0

Should be something like this:

UPDATE TABLE1 SET (adr1,adr3,nbqe) =
(SELECT table2.adr1,table2.adr2,table2.nbqe
 FROM table1
 INNER JOIN table2
 ON table1.cg= table2.cg AND table1.ce = table2.ce
);

also you didn't provide a where clause in your select so all the row successfully joined will be updated, is that what you you want?

edited Sep 21 '17 at 10:28

answered Sep 21 '17 at 10:02

Cyrille MODIANO

2,246
2
21
33

it is not what I want, no where because there is a join.. the select I provided returns 8 rows on the 80. This is what I want – mlwacosmos Sep 21 '17 at 10:11
There is nothing wrong with the syntax - inline view can be updated this way. It's a whole different problem. – Nick Krasnov Sep 21 '17 at 10:14
I want to change the eight rows from table 1 not everything – mlwacosmos Sep 21 '17 at 10:15
This is what will happen because of the join, I will update my answer. You can still run the update, check if it has the expected result, if not just issue a rollback, if it worked as expected then issue a commit – Cyrille MODIANO Sep 21 '17 at 10:17
Your query cannot work because the select returns 8 rowns that you cannot put in a value. – mlwacosmos Sep 21 '17 at 10:22
I updated my answer with you updated query, it will update every row matching your join, the table1 columns will be updated with matching rows from table2 – Cyrille MODIANO Sep 21 '17 at 10:32

How to get rid of ORA 01779 error :key preserved table

2 Answers2