Re: ORA-01779, update join view

xcure2k_at_gmail.com wrote:
> Hi there,
>
> I'm trying to solve an update, and simply can't find a way. It updates
> 1 table, that has a 3 column PK. See it here:
>
> update
> ( select
> t1.emp_cod t1emp,
> t1.cednte_cedn t1cedn,
> t1.titlo_noss_num t1noss,
> t1.titlo_num_versao,
> t2.emp_cod t2emp,
> t2.cednte_cedn t2cedn,
> t2.titlo_noss_num t2noss,
> t2.renda_dat,
> t2.renda_tx_equal_banco
> from
> tb_cob_renda t2,
> tb_titlo t1
> where
> t2.emp_cod = t1.emp_cod and
> t2.cednte_cedn = t1.cednte_cedn and
> t2.titlo_noss_num = t1.titlo_noss_num and
> t1.emp_cod = 1 and
> t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
> set titlo_num_versao = renda_tx_equal_banco;
>
> Here is the script for the 2 tables:
>
> CREATE TABLE "TB_TITLO" (
> "EMP_COD" NUMBER(4,0) NOT NULL,
> "CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
> "TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
> "TITLO_NUM_VERSAO" NUMBER(5,0)) ;
>
> ALTER TABLE "TB_TITLO" ADD ( CONSTRAINT PK_TITLO PRIMARY KEY (
> "EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;
>
> CREATE TABLE "TB_COB_RENDA" (
> "RENDA_DAT" DATE NOT NULL,
> "EMP_COD" NUMBER(4,0) NOT NULL,
> "CEDNTE_CEDN" NUMBER(10,0) NOT NULL,
> "TITLO_NOSS_NUM" NUMBER(11,0) NOT NULL,
> "RENDA_TX_EQUAL_BANCO" NUMBER(5)) ;
>
> ALTER TABLE "TB_COB_RENDA" ADD ( CONSTRAINT PK_RENDA PRIMARY KEY (
> "RENDA_DAT", "EMP_COD", "CEDNTE_CEDN", "TITLO_NOSS_NUM" )) ;
>
> Why, if the 2 tables are connected and using their primary keys, I
> always get the ORA-01779 (non key-preserved table)???
>
> The update itself a bit more complex, updates many columns, and using
> the:
>
> update tb_titlo t1
> set ( t1.titlo_num_versao ) =
> ( select renda_tx_equal_banco
> from tb_cob_renda t2
> where t2.emp_cod = t1.emp_cod
> and t2.cednte_cedn = t1.cednte_cedn
> and t2.titlo_noss_num = t1.titlo_noss_num
> and t2.renda_dat = to_date('2005-10-28','yyyy-MM-dd') )
> where t1.emp_cod = 1
> and exists ( select 1
> from tb_cob_renda
> where tb_cob_renda.emp_cod = t1.emp_cod
> and tb_cob_renda.cednte_cedn = t1.cednte_cedn
> and tb_cob_renda.titlo_noss_num =
> t1.titlo_noss_num
> and tb_cob_renda.renda_dat =
> to_date('2005-10-28','yyyy-MM-dd') );
>
> works, but gives me poor performance.
>
> Please!!!!!!!!! Help me!!!!
> I'm using Oracle8i Enterprise Edition Release 8.1.7.4.1
>
> PS. I prefer Oracle, but this specific issue was so simple in SQL
> Server... (update t1 from t1, t2 where...) ... :(

You can update tb_cob_renda here but not tb_titlo, because the same tb_titlo row could (so far as the database knows) appear against more than one tb_cob_renda. For example, of you join employees to departments you can update the employee side of the join but not departments.

In 9i you can use the BYPASS_UJVC hint (undocumented, unsupported, at your own risk), though I don't know whether it existed in 8i. Received on Wed Dec 28 2005 - 19:08:25 CST