Today, Zhongshan company lets the dispatching arrangement batch be seen remotely, because it is the same source program, making the formal library will not report errors, making the test library will report errors, thinking of the problem of data or database settings, so it debugs the source program, and the program will report errors when it comes to the data window of_update(). Obviously, it should be the fault of updating sql report, so take out the sql statement of data window, according to the business process of arranging batches, and finally extract it into an sql statement: UPDATE VIW_CK_BCARR SET ZUOY_STATE='D1', BOCI_NO='00000012'WHERE DANJ_NO='XXXXXX'; since it is an error to go from pb, take out a piece of sql statement and find that the newspaper "ORA-01779: can not be modified". The column corresponding to the non-key value saving table is obviously a background error. I think it should be related to the table key. So I look at the eight tables that VIW_CK_BCARR tried to associate. I find that there are no primary keys in the eight tables. It is obvious that the newly established library has some problems. After adding the primary keys of each table to the formal library, the statement is executed successfully, and then the batch arrangement is executed in the front desk. Error reporting, problem solving!

For table checks without primary keys for database checks, see http://blog.sina.com.cn/s/blog_b43f755b0101gkyb.html

However, this issue also gives me a new understanding of viwe updates. Before that, I only updated the single form of viwe. This time, I made a comprehensive summary of viwe, so as to avoid similar problems next time, I can take the updated sql statement as an attempt to look at it. The actual mechanism is the same.
Table test1

create table test1
(
id number,
gender varchar2(10)
);

insert into test1 values(1,'male');
insert into test1 values(2,'female');
insert into test1 values(3,'female');
insert into test1 values(4,'female');

Table test2

create table test2
(
id number,
gender varchar2(10)
);

insert into test2 values(2,'male');
insert into test2 values(3,'male');
insert into test2 values(5,'female');

Now that test1 has the same ID as test2, I want to update gender in test1 with gender in test2, using the following statement
 

UPDATE
(
SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
A.ID = B.ID
)
SET AG = BG;
ERROR AT LINE 6:
ORA-01779: CANNOT MODIFY A COLUMN WHICH MAPS TO A NON KEY-PRESERVED TABLE

The reason for this error is that the test2 table id used to update test1 is not unique in the result of the sub-query, so that a row in the updated object test1 may correspond to many rows in test2, so oracle does not know how to update it.

Solution
1. Add a primary key to test2.

SQL> ALTER TABLE TEST2 ADD PRIMARY KEY(ID);

TABLE ALTERED.

SQL> 
UPDATE
(
 SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
 A.ID = B.ID
)SET AG = BG;
2 ROWS UPDATED.

SQL>SELECT * FROM TEST1;

   --------------

1) Male
2) Male
3) Male
4) Women

SQL> ALTER TABLE TEST2 DROP PRIMARY KEY;
TABLE ALTERED.

2. Add a unique index to test2

SQL> CREATE UNIQUE INDEX IDX_TEST2 ON TEST2(ID);

INDEX CREATED.

SQL> UPDATE
 (
 SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
 A.ID = B.ID
 ) SET AG = BG;

2 ROWS UPDATED.

SQL> DROP INDEX IDX_TEST2;

INDEX DROPPED.

3. Using BYPASS_UJVC to enforce, BYPASS_UJVC can skip the key check of ORACLE.

SQL> UPDATE
(
 SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
 A.ID = B.ID
 )SET AG = BG;

2 rows updated.

Although this can be done, there are not unique data in test2, so test1 may be updated many times.

 

[Supplementary Tip] If you update table1 with table2, you must ensure that the results in table2 are unique. For example, you can add a primary key to table2. If table2 has two primary keys, the primary keys of table2 must be used to associate tables corresponding to table1 when updating. That's the same thing with a unique index.