SQL 无法修改映射到非键保留表的列时更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20976672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update tables when cannot modify a column which maps to a non key-preserved table
提问by clD
----- s.op_id= ------
| S |----------| OP |
----- op.op_id ------
|
| op.op_id = j.op_id
|
-----
| J |
-----
An oracle database has three tables journey, op_profileand special.
一个 oracle 数据库有三个表journey，op_profile和special.
journeyhas two columns journey_id(primary key)and op_id,
op_profilehas primary key op_idand
specialhas a column op_idwhich is a foreign keyon op_profileand a further column s_idwhich is the tables primary key.
journey有两列journey_id（主键）和op_id，
op_profile有主键op_id，
special有一列op_id是外键，op_profile另一列s_id是表主键。
I have tried to update all the op_idcolumns to equal the journey_idcolumn of the journeytable with the following sql usin sql developer:
我尝试使用以下 sql 使用 sql developer更新所有op_id列以等于表的journey_id列journey：
UPDATE (SELECT special.op_id, journey.journey_id, op_profile.op_id AS op
FROM special, journey, op_profile
WHERE special.op_id = journey.op_id AND journey.op_id = op_profile.op_id)
SET op_id = journey_id, op = journey_id;
which gives the following error
这给出了以下错误
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "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.
which seems to say that this operation is failing because of the keys of each table.
这似乎是说由于每个表的键，此操作失败。
Is this possible in this way, or another?
这是可能的，还是其他方式？
回答by Gordon Linoff
Yes this is possible using two update statements:
是的，这可以使用两个更新语句：
update s
set op_id = (select j.journey_id
from j
where j.op_id = s.op_id
);
and:
和：
update op
set op_id = (select j.journey_id
from j
where j.op_id = op.op_id
);
回答by SriniV
Add indexes on the following (if not available before)
添加以下索引（如果之前不可用）
- special.op_id
- journey.op_id
- op_profile.op_id
- special.op_id
- 旅程.op_id
- op_profile.op_id
This problem happens when it cannot obtain a stable set of rows for update.
当它无法获得一组稳定的行进行更新时，就会发生此问题。
