update view with composite PK

From: Thomas BLETON <tbleton_at_free.fr>
Date: 7 Sep 2001 02:36:56 -0700
Message-ID: <c3de42da.0109070136.6df278a5@posting.google.com>

Hi,

First of all, I am using Oracle 8.1.5

I have to write a SQL statement that will update one column from a first table, with values from a second table.

At first, I couldn't because of the ORA-01779 error (non key preserved table), and I read that the second table needs a pk to identify uniquely the rows in the first table.
This works perfectly with single primary keys, but I couldn't get it to work with composite primary keys.

Here are my tables :

SQL> create table t1 (c1 integer, c2 integer, c3 integer);

Table créée.

SQL> create table t2 (c1 integer, c2 integer, c3 integer, c4 integer);

Table créée.

I need to update t1.c3 with values in t2.c4, joining on c1, and where t2.c2 = 1

SQL> update (select t1.c3 t1c3, t2.c4 t2c4

  2          from t1, t2
  3          where t1.c1=t2.c1 and t2.c2=1)

  4 set t1c3=t2c4;
set t1c3=t2c4

*

ERREUR à la ligne 4 :
ORA-01779: Impossible modifier colonne correspondant à une table non protégée par clé

SQL> alter table t2 add primary key (c1);

Table modifiée.

SQL> update (select t1.c3 t1c3, t2.c4 t2c4, t2.c1 t2c1, t2.c2 t2c2

  2          from t1, t2
  3          where t1.c1=t2.c1 and t2.c2=1)

  4 set t1c3=t2c4;

0 ligne(s) mise(s) à jour.

SQL> alter table t2 drop primary key;

Table modifiée.

SQL> alter table t2 add primary key (c1, c2);

Table modifiée.

SQL> update (select t1.c3 t1c3, t2.c4 t2c4, t2.c1 t2c1, t2.c2 t2c2

  2          from t1, t2
  3          where t1.c1=t2.c1 and t2.c2=1)

  4 set t1c3=t2c4;
set t1c3=t2c4

*

ERREUR à la ligne 4 :
ORA-01779: Impossible modifier colonne correspondant à une table non protégée par clé

• End of code ===

t2.c2 has no equivalent in t1, so i can't use "t2.c2=t1.c2 and t1.c2=1".

Any help appreciated
Thanks

Tom Received on Fri Sep 07 2001 - 04:36:56 CDT