Re[2]: sql question
Jonathan,
My solution assumed that not every row in the table had duplicates.
That is why I had the count(*) in the having clause. The rows loaded
into the temp table would only be a subset of the original table.
Your rewrite of my solution would be more appropriate if a large
percentage of the rows had duplicates.
I like the idea of using the rowid though. It is a lot neater.
Regards
David Ashby
______________________________ Reply Separator _________________________________
Subject: Re: sql question
Author: Jonathan Leffler <johnl@informix.com> at WCA-INET
Date: 08/12/97 11:42 AM
On Mon, 8 Dec 1997 david.ashby@workcover.nsw.gov.au wrote:
}> Subject: sql question
}> Author: ministry of education <moe003@netvision.net.il> at WCA-INET
}> Date: 07/12/97 6:10 PM
}>
}> I have a table with multiple exact duplicate rows.No indexes. How do I
}> delete all duplicate rows to get a single occurrence for each row?
} Try this:
}
} - select distinct col1, col2 .... , count(*)
} from table
} group by col1, col2 ....
} having count(*) > 1
} into temp temp_dulicates;
}
} - delete from table
} where col1||col2 .... in
} (select col1||col2 ....
} from temp_duplicates);
}
} - insert into table
} select col1, col2 .... from temp_duplicates;
}
} Warning TEST this before running!
That's a complicated way of writing:
BEGIN WORK;
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
DELETE FROM Table WHERE 1 = 1;
INSERT INTO Table SELECT * FROM Temp1;COMMIT WORK;
This is a reasonable solution for small to medium tables when you have
enough disk space available for the whole temporary table.
If you don't, then you have to get fancy with ROWID, assuming that you are
not dealing with a fragmented table, of course! I attach an old answer to
this which works with ROWIDs.
Is this in the FAQ? If not, I think it should be added. Unless someone
else has a tidier solution...
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>
<snip>