Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!ubiquity From: ubiquity@cs.utexas.edu (Richard Hoffman) Newsgroups: comp.databases Subject: Re: Duplicated tuples problem (Oracle/SQL) Message-ID: <16608@cs.utexas.edu> Date: 7 Jan 91 14:08:20 GMT References: <772@keele.keele.ac.uk> <12224@sybase.sybase.com> <775@keele.keele.ac.uk> <946@kps.UUCP> Organization: Ubiquitous Productions, R. Hoffman Proprietor Lines: 30 > Q: Is there a way to automatically delete duplicate tuples from a > table/all tables in a db using SQL? When you say "automatically", do you mean "whenever they occur, without human intervention"? If so, the answer is "no", but you can automatically *prevent* duplicates using any of the following: + a UNIQUE index on one or more columns in the table + a UNIQUE clause on one or more column definitions + a PRIMARY KEY clause on the table definition The first is widely available; the second is part of the ANS89 standard, and the third is part of the Integrity Enhancement of ANS89. If you simply want some SQL to delete duplicate tuples once they have occured, you will need more than one SQL statement. Create a duplicate of the table you want to clean up, and then insert into the new table with a subselect that selects all the distinct rows in the old table. The way you construct this subselect will vary depending on your product. With OS/2 EE Database Manager, the simplest way would be: INSERT INTO NEW ((SELECT * FROM OLD) UNION (SELECT * FROM OLD)) since UNION tosses duplicates. -- Richard Hoffman IBM Entry Systems Division (512) 823-1822 1529 Ben Crenshaw Way Austin, TX 78746 "Life is a gamble at terrible odds; (512) 327-9232 if it were a bet you wouldn't take it" (Tom Stoppard)