Re: Need to delete duplicate rows

>> I would be grateful if anyone could please tell me how I could use
SQL to remove the duplicate rows from my table. <<

Products with non-relational "features" like ROWID or some version of cursors make it easy. It is a pain in a real SQL implementation. For Oracle, use the ROWID to make the groups of duplicates different.

However, in general you might try something where you first build a table of the duplicted rows:

 CREATE TABLE Redundants
 ( ....);

 INSERT INTO Redundants
 SELECT * FROM Foobar
 GROUP BY ....
HAVING COUNT(*) > 1:  DECLARE Dups AS CURSOR FOR
 SELECT F1.*
   FROM Foobar AS F1
  WHERE EXISTS

        (SELECT *
           FROM Redundants AS R1
         WHERE R1.a = F1.a
           AND R1.b = F1.b
           ...);

Then add up logical to sklp over the first copy and then do

 DELETE FROM Foobar
  WHERE CURRENT OF Redundant;

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 20 2000 - 00:00:00 CEST