Re: Delete behaviour

On 4 Mrz., 14:55, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Serge Rielau" <srie..._at_ca.ibm.com> schreef in berichtnews:6351m3F24f4e1U1_at_mid.individual.net...
>
>
>
> > DA Morgan wrote:
> >> Gokul wrote:
> >>> Maxim, thanks for your response. Posting DDLs and inserts on Daniel's
> >>> request.
>
> >>> create table t1 (i number primary key);
>
> >>> create table t2 (i number primary key);
>
> >>> insert into t1 values (1);
>
> >>> insert into t2 values (1);
>
> >>> commit;
> >> As both Maxim and I indicated this is documented behaviour.
> > If memory serves me correctly then DELETE through JOIN is only meant to
> > work if there is exactly ONE key preserving table in the join. And that
> > ONE key presreving table will be the subject of the DELETE.
> > It appears that is not the case here!
> > Instead the DBMS appears to chose the subject of the DELETE operation by
> > first appearance in the syntax.
>
> > Are you sure that is documented behavior? I wouldn't doubt if this were
> > the SQL Server group ...
>
> > Cheers
> > Serge
> > --
> > Serge Rielau
> > DB2 Solutions Development
> > IBM Toronto Lab
>
> Indeed!
> From the docs:
>
> "In the following view, a DELETE operation is permitted, because although
> there are two key-preserved tables, they are the same table. That is, the
> key-preserved table is repeated. In this case, the delete statement operates
> on the first table in the FROM list (e1, in this example):
> CREATE VIEW emp_emp AS
> SELECT e1.ename, e2.empno, e2.deptno
> FROM emp e1, emp e2
> WHERE e1.empno = e2.empno;
> "
> So this is a different case: the kpt is repeated. Though I don't see the
> difference (here) whether a delete is taking place on the first or second
> table, they're the same table...From what I read from these docs, a delete
> should not be permitted in the Op's case.Shakespeare

Well, maybe another pointer to documentation makes it a bit more clear.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510

Notes on Updatable Views
...
For a DELETE statement, if the join results in more than one key- preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION. From the previous link the phrase
"You can delete from a join view provided there is one and only one key-preserved table in the join"
seems to be not very accurate wording, *may* instead of *can* would be more appropriate (just imho)

Best regards

Maxim Received on Tue Mar 04 2008 - 09:39:42 CST