Re: Skill testing SQL question

IMO, the point is that this situation should only occur with (very) old data and hence the solution with a straight SQL is inappropriate. With new data, you'd create a UNIQUE constraint, wouldn't you?

But with old data, you'd have to examine the data to see why dupes could ever have occured. Furthermore, in an old database your TEMP segment could be too small to use the proposed SQL solutions. The most crash-proof solution would be to (1) create an non-unique index (2) write a PL/SQL routine using two cursors (one for the full table scan; the other -using the index!- to look up the dupes and delete all but the very first) and (3) recreate the index as unique. Steps 1 and 3 are optional. This is an O*O solution (Cartesian product), but it will work on a table of any size, and if it is a one time clean up, what's the issue of performance?

--
Dirk Bellemans
Modify email address to reply (use .be instead of .belgium)

Jonathan Lewis wrote in message
<941792675.26450.0.nnrp-09.9e984b29_at_news.demon.co.uk>...
>
>It's not a good question, it's either a
>very bad question or a totally inspired
>question.
Received on Tue Nov 09 1999 - 15:49:42 CST