Re: Delete behaviour

From: Shakespeare <>
Date: Tue, 4 Mar 2008 14:55:50 +0100
Message-ID: <47cd54eb$0$14342$>

"Serge Rielau" <> schreef in bericht
> 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

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 Received on Tue Mar 04 2008 - 07:55:50 CST

Original text of this message