Re: Skill testing SQL question
Date: Tue, 9 Nov 1999 22:49:42 +0100
Message-ID: <80a4su$tq4$2_at_news1.skynet.be>
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>...Received on Tue Nov 09 1999 - 22:49:42 CET
>
>It's not a good question, it's either a
>very bad question or a totally inspired
>question.