Subject | Re: need help deleting duplicate rows |
---|---|
Author | rvellacott@passfield.co.uk |
Post date | 2001-11-05T14:19:48Z |
Try this. I haven't tested it but it should be more or less right.
ALTER TABLE TABLE_1 ADD TEMP_KEY INTEGER;
CREATE GENERATOR TEMP_GENERATOR;
UPDATE TABLE_1 SET TEMP_KEY = GEN_ID(TEMP_GENERATOR,1);
CREATE PROCEDURE KILL_DUPLICATES AS
declare variable FINISHED CHAR(1);
declare variable TEMP_KEY INTEGER;
declare variable KEY_FIELD INTEGER;
declare variable DUPS INTEGER;
BEGIN
FINISHED = 'F';
WHILE (FINISHED = 'F') DO
BEGIN
FINISHED = 'T';
FOR SELECT KEY_FIELD, MAX(TEMP_KEY), COUNT(*) FROM TABLE_1
GROUP BY KEY_FIELD
HAVING COUNT(*) > 1
INTO :KEY_FIELD,:TEMP_KEY,:DUPS DO
BEGIN
FINISHED = 'F';
DELETE FROM TABLE_1
WHERE TEMP_KEY = :TEMP_KEY;
END
END
END
EXECUTE PROCEDURE KILL_DUPLICATES
ALTER TABLE TABLE_1 ADD TEMP_KEY INTEGER;
CREATE GENERATOR TEMP_GENERATOR;
UPDATE TABLE_1 SET TEMP_KEY = GEN_ID(TEMP_GENERATOR,1);
CREATE PROCEDURE KILL_DUPLICATES AS
declare variable FINISHED CHAR(1);
declare variable TEMP_KEY INTEGER;
declare variable KEY_FIELD INTEGER;
declare variable DUPS INTEGER;
BEGIN
FINISHED = 'F';
WHILE (FINISHED = 'F') DO
BEGIN
FINISHED = 'T';
FOR SELECT KEY_FIELD, MAX(TEMP_KEY), COUNT(*) FROM TABLE_1
GROUP BY KEY_FIELD
HAVING COUNT(*) > 1
INTO :KEY_FIELD,:TEMP_KEY,:DUPS DO
BEGIN
FINISHED = 'F';
DELETE FROM TABLE_1
WHERE TEMP_KEY = :TEMP_KEY;
END
END
END
EXECUTE PROCEDURE KILL_DUPLICATES