oracle 11，更新联接

本文介绍了oracle 11，更新联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个这样的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;