oracle 11,更新联接 [英] oracle 11, update join
问题描述
我有一个这样的SQL语句:
I have a SQL-Statement like this:
UPDATE tbl
SET old_ht = new_ht,
old_ttc = new_ttc
from table1 tbl
join table2 temp ON trim(temp.val_code) = trim(tbl.val_code)
AND trim(temp.tv_code) = trim(tbl.tv_code)
INNER JOIN table3 tbl3 ON trim(tbl3.oma_CODE) =trim(temp.oma_CODE)
AND trim(tbl3.men_CODE) =trim(temp.men_CODE)
AND trim(tbl3.gov_CODE) =trim(gov.BRD_CODE)
and tbl3.fld_id = tbl.fld_id ;
但是看来Oracle不支持此语法.
But it seems that Oracle does not support this syntax.
我也尝试过:
UPDATE ( select tbl.cost_ht as old_ht, temp.cost_ht as new_ht,tbl.cost_ttc as old_ttc
, temp.cost_ttc as new_ttc
from table1 tbl
join table2 temp ON trim(temp.val_code) = trim(tbl.val_code)
AND trim(temp.tv_code) = trim(tbl.tv_code)
INNER JOIN table3 tbl3 ON trim(tbl3.oma_CODE) =trim(temp.oma_CODE)
AND trim(tbl3.men_CODE) =trim(temp.men_CODE)
AND trim(tbl3.gov_CODE) =trim(gov.BRD_CODE)
and tbl3.fld_id = tbl.fld_id )
SET old_ht = new_ht, old_ttc = new_ttc
但是我得到这个错误:
错误报告:
SQL错误:
ORA-01779:无法修改映射到非键保留表的列.00000-无法修改映射到非键保留表的列"
*原因:试图插入或更新联接视图的列, 映射到非保留键的表.
*操作:直接修改基础基表.
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.
推荐答案
就这么简单.通过此索引为您的引擎提供更大的信心.
As simple as that.. Provide more confidence to your engine by this index.
CREATE UNIQUE INDEX IDX1
ON TABLE1 ( VAL_CODE,TV_CODE );
CREATE UNIQUE INDEX IDX2
ON TABLE2 ( VAL_CODE,TV_CODE, OMA_CODE ,MEN_CODE );
CREATE UNIQUE INDEX IDX3
ON TABLE3 ( OMA_CODE ,MEN_CODE, GOV_CODE );
然后尝试运行查询
UPDATE
(SELECT
TBL.COST_HT AS OLD_HT,
TEMP.COST_HT AS NEW_HT,
TBL.COST_TTC AS OLD_TTC,
TEMP.COST_TTC AS NEW_TTC
FROM
TABLE1 TBL
JOIN TABLE2 TEMP
ON TRIM ( TEMP.VAL_CODE ) = TRIM ( TBL.VAL_CODE )
AND TRIM ( TEMP.TV_CODE ) = TRIM ( TBL.TV_CODE )
INNER JOIN TABLE3 TBL3
ON TRIM ( TBL3.OMA_CODE ) = TRIM ( TEMP.OMA_CODE )
AND TRIM ( TBL3.MEN_CODE ) = TRIM ( TEMP.MEN_CODE )
AND TRIM ( TBL3.GOV_CODE ) = TRIM ( GOV.BRD_CODE )
AND TBL3.FLD_ID = TBL.FLD_ID)
SET
OLD_HT = NEW_HT,
OLD_TTC = NEW_TTC;
这篇关于oracle 11,更新联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!