In the user1.btable table, user1.btable a unique constraint for 4 fields: c, d, e, f .

Request

 update (select a.bid a$bid, b.bid b$bid from user1.atable a join user1.btable b on ac = bc and ad = bd and ae = be and bf = 0) set a$bid = b$bid;

gives an error message

ORA-01779: cannot change column, cat. Displays non key-preserved table

Why? After all, usually this error occurs for the following reason

Oraklu need to uniquely identify the record that needs to be changed. And for this, the subquery must use all the columns in a unique key. In addition, in the first case, you may have a problem with the fact that several rows from Balitsa B will be selected for one record from Table A and then it will not be clear to Oracle which of records B to take the bid - Mike

And if user1.atable add the f field to the user1.atable table, and change the join condition to

  on ac = bc and ad = bd and ae = be and bf = af

The request does not give an error.

Issuing SELECT * FROM V$VERSION

Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production
PL / SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

  • Well, actually by the way, as written in the text of the error. Oraklu need to uniquely identify the record that needs to be changed. And for this, the subquery must use all the columns in a unique key. In addition, in the first case, you may have a problem with the fact that several rows from Balitsa B will be selected for one record from table A and then it will not be clear to Oracle which of records B to take the bid - Mike
  • @Mike, 1) uniquely identify it as? in the atable table there are no indices for these fields, which means both in the first and in the second query several rows of atable can be selected for one btable. 2) Multiple lines from B cannot be issued for one A in both queries, since I specify the values ​​for each of the fields of unique constraint in the join condition - 4per
  • Inattentively read, it seemed to me you unique on the first table. And just the condition af = bf is not enough, is it necessary to include f in the sample list in order for it to work? - Mike
  • conditions af = bf are sufficient, there is simply no such field initially in the atable table, it has to be added to get around the problem. And I want to know the answer to the question. - 4per
  • 2
    Yes, a mystery. I can only suggest to rewrite the request as update user1.atable a set bid=(select nvl(max(b.bid),a.bid) from user1.btable b where ac = bc and ad = bd and ae = be and bf = 0) - Mike

2 answers 2

In the scheme described by you, everything should work created a similar test circuit:

 CREATE TABLE TEST_TABLEA( "C" VARCHAR2(10), "D" VARCHAR2(10), "E" VARCHAR2(10), "F" VARCHAR2(10), "BID" VARCHAR2(10)); CREATE TABLE TEST_TABLEB( "C" VARCHAR2(10), "D" VARCHAR2(10), "E" VARCHAR2(10), "F" VARCHAR2(10), "BID" VARCHAR2(10)); ALTER TABLE TEST_TABLEB add constraint CONSTR unique (C, D, E, F) using index;

And the query (which you indicated in the question) works correctly on it, if there is a constraint, but if it is not there, then the exception ORA-01779 .

To avoid the error, try the following query:

 update test_tablea set bid = (select b.bid from test_tableb b where test_tablea.c = bc and test_tablea.d = bd and test_tablea.e = be and bf = 0) where exists( select b.bid from test_tableb b where test_tablea.c = bc and test_tablea.d = bd and test_tablea.e = be and bf = 0)

or its analogue with nvl proposed in the comments, but then you will have extra update (s).

After checking on different versions of the database, it was possible to establish the following join update NOT working on Oracle versions:

  • Oracle Database 10g Enterprise Edition Release 10.2.0.4.0,
  • Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

Works for:

  • Oracle Database 11g Enterprise Edition Release 11.2.0.3.0,
  • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0,
  • Oracle Database 12c Standard Edition Release 12.1.0.2.0.
  • executed your script at the top. Then request from your question. The same mistake. What is your version of Oracle? his added to the question - 4per
  • @ 3per, I tested on Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production. - Mikhailov Valentine
  • Can we make a decision with you that the error has been eliminated in later versions? - 4per
  • @ 3per, I'll look for your version now and try on it! - Mikhailov Valentine
  • one
    @ 3per, checked, you were right on newer versions running on old ones not. - Mikhailov Valentine

In addition to the answer Mikhailov Valentine. On 10-ke the query does not work, but on SE 11.2.0.3.0 it works. Apparently, in a new version, when parsing a query, some classes of expressions are allowed, in addition to direct references to the fields of the base table. So, for simple expressions that are calculated at the parsing stage, and as a result of generating a constant, the query works: bf = 0 or case when 0 is null then 0 else 0 end . But if this expression contains references to other fields, it will not work: bf = af - af , case when af is null then af else af end

Works for the condition bf = af , but it does not work for the conditions: bf = af + 0 , bf = nvl(af, 0)