Board index » delphi » update via VIEW ORACLE 8.1.7

update via VIEW ORACLE 8.1.7

Hello,

does anybody know if it is possible to update a table ABC via a view V_ABC
that joins the table ABC with XYZ.

All fields in SET and WHERE of the UPDATE-Statement are fields of ABC.
The following script is for SQLPlus and drops also the created tables.

Quote

CREATE TABLE ABC (
  THE_KEY    VARCHAR2(4),
  THE_VALUE  NUMBER
)
/
CREATE TABLE XYZ (
  THE_KEY    VARCHAR2(4),
  COL1       VARCHAR2(4)
)
/
INSERT INTO ABC (THE_KEY,THE_VALUE)
  VALUES('123',567)
/
INSERT INTO XYZ (THE_KEY,COL1)
  VALUES('123','abc')
/
CREATE OR REPLACE VIEW V_ABC AS SELECT a.THE_KEY, x.COL1, a.THE_VALUE
FROM ABC a, XYZ x
Where x.THE_KEY = a.THE_KEY
/
UPDATE V_ABC SET THE_VALUE=814025
where THE_KEY='123';
-- ->ORA-01779: cannot modify a column which maps to a non key-preserved
table

drop TABLE ABC;
drop TABLE XYZ;

Quote

Is there any way or exists other DBMS that are able to do it?

Best regards
Dietmar Brueckmann

 

Re:update via VIEW ORACLE 8.1.7


Do you have primary key defined for table ? If you define PK for xyz it will
work
Jaro

Quote
"Dietmar Brckmann" <brueckm...@kzvth.ef.uunet.de> wrote in message

news:3b4c24b1_1@dnews...
Quote
> Hello,

> does anybody know if it is possible to update a table ABC via a view V_ABC
> that joins the table ABC with XYZ.

> All fields in SET and WHERE of the UPDATE-Statement are fields of ABC.
> The following script is for SQLPlus and drops also the created tables.

> CREATE TABLE ABC (
>   THE_KEY    VARCHAR2(4),
>   THE_VALUE  NUMBER
> )
> /
> CREATE TABLE XYZ (
>   THE_KEY    VARCHAR2(4),
>   COL1       VARCHAR2(4)
> )
> /
> INSERT INTO ABC (THE_KEY,THE_VALUE)
>   VALUES('123',567)
> /
> INSERT INTO XYZ (THE_KEY,COL1)
>   VALUES('123','abc')
> /
> CREATE OR REPLACE VIEW V_ABC AS SELECT a.THE_KEY, x.COL1, a.THE_VALUE
> FROM ABC a, XYZ x
> Where x.THE_KEY = a.THE_KEY
> /
> UPDATE V_ABC SET THE_VALUE=814025
> where THE_KEY='123';
> -- ->ORA-01779: cannot modify a column which maps to a non key-preserved
> table

> drop TABLE ABC;
> drop TABLE XYZ;

> Is there any way or exists other DBMS that are able to do it?

> Best regards
> Dietmar Brueckmann

Other Threads