SQL Oracle：多个表更新 => ORA-01779：无法修改映射到非键保留表的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9164586/
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
Oracle: multiple table updates => ORA-01779: cannot modify a column which maps to a non key-preserved table
提问by Revious
I've made a Join between tables. I simply want to take the values from some data and put into the other columns. But it doesn't work. How could I rewrite the query? I want to OVERWRITE the values in wkfc table with the values from swkf.
我在表之间进行了连接。我只是想从一些数据中获取值并放入其他列。但它不起作用。我怎么能重写查询？我想用来自 swkf 的值覆盖 wkfc 表中的值。
I know it's right how can I make Oracle simply do it?
我知道这是正确的，我怎样才能让 Oracle 简单地做到这一点？
UPDATE (
SELECT --FROM
swkf.swkf_stato_workflow_id "swkf_swkf_stato_workflow_id",
swkf.swkf_data_ini "swkf_swkf_data_ini",
swkf.swkf_versione "swkf_swkf_versione",
swkf.spwkf_stato_pubblico_id "swkf_spwkf_stato_pubblico_id",
swkf.spwkf_data_ini "swkf_spwkf_data_ini",
swkf.spwkf_versione "swkf_spwkf_versione",
--TO
wkfc.swkf_stato_workflow_id "wkfc_swkf_stato_workflow_id",
wkfc.swkf_data_ini "wkfc_swkf_data_ini",
wkfc.swkf_versione "wkfc_swkf_versione",
wkfc.spwkf_stato_pubblico_id "wkfc_spwkf_stato_pubblico_id",
wkfc.spwkf_data_ini "wkfc_spwkf_data_ini",
wkfc.spwkf_versione "wkfc_spwkf_versione"
--
FROM wkfb_stati_workflow swkf, wkf_cronologia wkfc
WHERE twkf_tipo_workflow_id =
(SELECT twkf_tipo_workflow_id
FROM wkf_istanze_workflow wkfi, RET_PUNTI_EROGAZIONE RPUN
WHERE RPUN.PUN_PUNTO_EROGAZIONE_COD = '8001375567' --codice puntero
AND RPUN.PUN_PUNTO_EROGAZIONE_ID = wkfi.ogg_oggetto_id
AND wkfi.tog_tipo_oggetto_id = 'RET_PUN1'
AND wkfi.WKFI_FLAG_ANN = 'N')
AND swkf_descrizione = '(O)Occupato'
AND wkfc.wkfc_cronologia_id = 'ApAJ0qCudNphjLxj'
) a1
set
"wkfc_swkf_stato_workflow_id" = "swkf_swkf_stato_workflow_id" ,
"wkfc_swkf_data_ini" = "swkf_swkf_data_ini" ,
"wkfc_swkf_versione" = "swkf_swkf_versione" ,
"wkfc_spwkf_stato_pubblico_id" = "swkf_spwkf_stato_pubblico_id" ,
"wkfc_spwkf_data_ini" = "swkf_spwkf_data_ini" ,
"wkfc_spwkf_versione" = "swkf_spwkf_versione" ;
It's the same of doing as follow, but he have to find the values by itself.
和下面做一样，但是他必须自己找到值。
UPDATE wkf_cronologia
SET swkf_stato_workflow_id = 'o3gE1tlSdcDIC6FF',
swkf_data_ini = TO_TIMESTAMP ('19-06-2010 18:28:10,556000000','DD-MM-RRRR HH24:MI:SS,FF'),
swkf_versione = 0,
SPWKF_STATO_PUBBLICO_ID = '*1UNICOO',
SPWKF_DATA_INI = TO_TIMESTAMP ('01-01-0001 00:00:00,000000000', 'DD-MM-RRRR HH24:MI:SS,FF'),
SPWKF_VERSIONE = 0
WHERE wkfc_cronologia_id = 'ApAJ0qCudNphjLxj'; --id del record di cronologia da aggiornare (estratto nella prima query)
回答by kevin cline
If you want to UPDATE (SELECT ... FROM A INNER JOIN B ON condition) SET A.X = ...then the join condition must constrain all the columns of some uniqueness constraint on B to a single value.
如果您想，UPDATE (SELECT ... FROM A INNER JOIN B ON condition) SET A.X = ...那么连接条件必须将 B 上某个唯一性约束的所有列约束为单个值。
回答by Kanagavelu Sugumar
Firstly We cannot update two tables using single query through join view, We can opt DB Procedure in that case.
首先，我们不能通过连接视图使用单个查询更新两个表，在这种情况下我们可以选择 DB Procedure。
ORA-01776: cannot modify more than one base table through a join view
01776. 00000 - "cannot modify more than one base table through a join view"
*Cause: Columns belonging to more than one underlying table were either
inserted into or updated.
But we can use join to compare the base table with reference table and we can update one table.
但是我们可以使用join来比较基表和参考表，我们可以更新一张表。
UPDATE
(
SELECT t1.d1 x, t2.d1 y FROM t1, t2 WHERE t1.d2 = t2.d2 AND t2.d2 = 2
) t3
SET t3.x = 6;
-- t3.y = 1; this is wrong updating multiple table
Now performing this join (t1.d2 = t2.d2) should happen on unique columns. Here both tables d2 column should be unique.
现在执行这个连接 (t1.d2 = t2.d2) 应该发生在唯一的列上。这里两个表 d2 列都应该是唯一的。
Else result in below
否则结果如下
Oracle: multiple table updates => ORA-01779:
cannot modify a column which maps to a non key-preserved table
Below URL explains how to perform this join on non unique columns
下面的 URL 解释了如何在非唯一列上执行此连接
