Re: sql question

Michael Cressey wrote: > > Depending on how big this table is you might be better off > copying the data into a file and using an operating system > command to remove the duplicate rows. (sort -u on unix). > > Once this has been done you can copy the data back in to the > database. This is invariably quicker than letting the database > engine do the work. > > Make sure you've got a good backup first though! Actually, resorting to the OS should be the last thing you ought to try. How large a table it is isn't that much an issue as how much space you have left in the database that the table resides in. And this would only be the case if you were to try a 'select distinct ...' of the table into another table (i.e. make a copy with the duplicate rows removed). You could remove the duplicates by entering SQL to find and delete them or creating an index that would remove them as part of its creation process. This, however, depends on the specifics of the database server implementation. Using Unix (or the OS platform, since it may not be Unix), just adds further overhead - more commands to enter and more potential points of failure. You'd also need the space if the table was large. The sort command would be slower since it would have to work on plain text and the options to define which columns to sort on becomes tortuous the more columns you want to cover. Overall, the entire process would also be slower since you now have to add the time to copy the data out and load it back in afterwards. -am