Re: Delete behaviour
Date: Tue, 4 Mar 2008 14:55:50 +0100
Message-ID: <47cd54eb$0$14342$e4fe514c@news.xs4all.nl>
"Serge Rielau" <srielau_at_ca.ibm.com> schreef in bericht
news: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 Received on Tue Mar 04 2008 - 07:55:50 CST