Hi Guys How can I delete a duplica 1

[dickiebird]

Hi Guys
How can I delete a duplicate row from a Sybase table where a row exists that is identical to the first except for a single column value
ie I want to remove the second row, as below :-

1378980 AG Mar 5 2002 control NULL 1 Test extract
1378980 AG Mar 5 2002 control NULL 2 Test extract

or remove the second and third 'copies' , as below :-

1378981 AG Mar 5 2002 control NULL 1 Test extraction
1378981 AG Mar 5 2002 control NULL 2 Test extraction
1378981 AG Mar 5 2002 control NULL 3 Test extraction

Thanks in advance
DB
Dickie Bird
db@dickiebird.freeserve.co.uk

 

[PruSQLer]

Do the duplicate rows actually contain a field that gets incremented by 1, as in your example? Somehow I suspect not since that would be too easy.

 

[bperry]

This works in MS SQL Server, and I think will probably work in Sybase too:

You didn't provide column names, so I made some up. 'TagId' refers to the first left-hand column (137....), and 'SeqNo' is the second-last column (1,2,3 etc)

It looks like you want to keep the lowest SeqNo is a group.

(untested)
Delete w
From MyTable w INNER JOIN MyTable Dups
ON w.TagId = Dups.TagId
Where w.SeqNo > Dups.Seqno

----------------------------
Hope this gives you some ideas,
bp

 

[dickiebird]

Thanks
Its getting used to using the same table twice in a query that confounded me !
DB ;-) Dickie Bird
db@dickiebird.freeserve.co.uk