Re: Delete behaviour
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Tue, 04 Mar 2008 08:38:07 -0500
Message-ID: <6351m3F24f4e1U1@mid.individual.net>
>> 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.
Date: Tue, 04 Mar 2008 08:38:07 -0500
Message-ID: <6351m3F24f4e1U1@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 LabReceived on Tue Mar 04 2008 - 07:38:07 CST