sql - Oracle:多个表更新 => ORA-01779:无法修改映射到非键保留表的列

Question

我在表之间进行了连接。我只是想从一些数据中获取值并放入其他列中。但它不起作用。我怎样才能重写查询？我想用 swkf 中的值覆盖 wkfc 表中的值。

我知道这是对的，我怎样才能让 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"  ;

和follow是一样的，只是他要自己找值。

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)

Answer 1

如果您想 UPDATE (SELECT ... FROM A INNER JOIN B ON condition) SET A.X = ... 那么连接条件必须将 B 上某些唯一性约束的所有列约束为单个值。