Archives of the TeradataForum

Message Posted: Wed, 28 May 2003 @ 15:26:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, May 28, 2003 11:23 -->

Hi,

I have a table and I want to be able to identify when an Type A record is followed by a type B record, for the same customer, and delete them both. I have devised the SQL to identify both rows but then need to delete them. I tried using a derived table, but it will not let me use them in joins. The other way I thought was to use a Insert into a temporary table, and then delete from the first table using a join to this new temporary table and then delete from it.

Does anyone have any other ideas??

So I have a union :-

Select  sequence_number,
        name,
        address,
        age,
        type
from    customer customer1,
        customer customer2
where   customer1.name = customer2.name
and     customer1.address = customer2.address
and     customer1.age = customer2.age
and     customer1.type = 'a'
and     customer1.sequence_number = customer2.sequence_number - 1
union
Select  sequence_number,
        name,
        address,
        age,
        type
from    customer customer1,
        customer customer2
where   customer1.name = customer2.name
and     customer1.address = customer2.address
and     customer1.age = customer2.age
and     customer2.type = 'b'
and     customer1.sequence_number + 1 = customer2.sequence_number

This should bring me back two rows. What I need to know is how do I delete them both - prefrably without having to insert into any other tables??

Any help would be appreciated.

Thanks

	<Prev	Next>		<<First	<Prev	Next>	Last>>