ORA-01779, update join view

From: <xcure2k_at_gmail.com>
Date: 28 Dec 2005 07:23:47 -0800
Message-ID: <1135783427.459127.70150@g43g2000cwa.googlegroups.com>

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...) ... :( Received on Wed Dec 28 2005 - 09:23:47 CST