Archives of the TeradataForum

Message Posted: Thu, 19 Aug 2004 @ 19:19:06 GMT


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

Subj:		Re: DELETE recommendations?

From:		Dieter Noeth

Anomy Anom wrote:

Table 1 - has 3 columns as part of PK and 3 non-PK columns

          PK1       PK2       PK3       COL1    COL2    COL3
          A          B          C1        1          2          3
          A          B          C2        1          2          3
          A          C          D          1          2          3
          X          Y          Z          1          2          3
          P          Q          R          1          2          3

I need to delete the records from the table which have matching PK1 and PK2 but different PK3. eg - first 2 records in the above list.

A direct translation of your description:

     delete from tab
     where (pk1, p2) in
     (select pk1,pk2
       from tab
       group by 1,2
       having min(pk3) <> max(pk3)
     )

or probably easier, if (PK1,PK2,PK3) is unique/Primary Key:

     delete from tab
     where (pk1, p2) in
     (select pk1,pk2
       from tab
       group by 1,2
       having count(*) > 1
     )

There may be case when there are more than 2 rows with such combination.

All those rows will be deleted.

There can be multiple such combinations as well.

Dieter


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

Attachments

Library

Quick Reference