Re: Delete behaviour
Date: Tue, 4 Mar 2008 14:55:50 +0100
"Serge Rielau" <srielau_at_ca.ibm.com> schreef in bericht
> DA Morgan wrote:
>> Gokul wrote:
>>> Maxim, thanks for your response. Posting DDLs and inserts on Daniel's
>>> create table t1 (i number primary key);
>>> create table t2 (i number primary key);
>>> insert into t1 values (1);
>>> insert into t2 values (1);
>> 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 ...
> 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