On 2 Nov 2001, at 20:57, Mark Meyer wrote:

> hello everyone..
>
> i am new to IB.
>
> as a result of a very large conversion project - we have a
> Service_fees table that has several instances of dupicate rows.
> originally we thought it would be easy to identify and delete the dups
> - but this has not been the case.
>

This is why a lot of DBAs will force a unique key on all tables, if
there is no unique key, then you use a generator to create an auto-
incrementing key field which then becomes a unique value. I often
use auto-incrementing keys, where ever there isn't a clear single
field key, then use multi-field indexes on the multi-part key.

> we were able to identify them easily enough with the following sql
> (see below) - but deleting them has been more of a challenge.
>
> i have tried writing a delphi program - but we keep getting an
> exception when we try to delete one of the dups. basically the error
> message from delphi is - i was expecting 1 row but i got back multiple
> rows - which one do you really want me to delete.
>
> CREATE TABLE SERVICE_FEES (
> SERVICE_KEY REQUIRED_LONG,
> FEE_KEY REQUIRED_LONG,
> FEE_AMOUNT MONIES,
> WAIVED CHAR_1,
> FLIGHT_KEY REQUIRED_LONG
> )
>
> CREATE ASCENDING INDEX SERVICE_FEESINDEX1 ON SERVICE_FEES
> (SERVICE_KEY, FLIGHT_KEY)
>

Okay create a new temporary table, which has the same structure
as above, then pump the data out of the old table, drop the old one,
(and the index) and create a new table that looks like this:

--------- cut here ------------------------

CREATE TABLE SERVICE_FEES (
SERVICE_FEE_ID INTEGER NOT NULL,
SERVICE_KEY REQUIRED_LONG,
FEE_KEY REQUIRED_LONG,
FEE_AMOUNT MONIES,
WAIVED CHAR_1,
FLIGHT_KEY REQUIRED_LONG,
CONSTRAINT SERVICE_FEES_PK PRIMARY KEY
(SERVICE_FEE_ID));

CREATE ASCENDING INDEX SERVICE_FEESINDEX1 ON
SERVICE_FEES (SERVICE_KEY, FLIGHT_KEY,
SERVICE_FEE_ID)

CREATE GENERATOR SERVICE_FEE_GEN;

SET TERM ^ ;
CREATE TRIGGER SERVICE_GEN_TRIGGER FOR
SERVICE_FEES
ACTIVE BEFORE INSERT POSITION 100
AS
BEGIN
IF (( NEW.SERVICE_FEE_ID = 0 ) OR (
NEW.SERVICE_FEE_ID IS NULL )) THEN
NEW.SERVICE_FEE_ID = GEN_ID(SERVICE_GEN,1);
END ^
SET TERM ; ^

--------- cut here ------------------------

Now pump the data out of your temporary table into this new one,
and your problem is resolved, because now you can hunt down
your duplicates, get the SERVICE_FEE_IDs of the duplicates, and
feed those into your delete statement.

I made one change to your index, IB likes unique keys in indexes,
by adding the SERVICE_FEE_ID onto the end of the index, it
should perform better.

Your program can run the script you had for finding the duplicates,
but retrieve the SERVICE_FEE_ID for the duplicates, and delete
them.

I don't know which Delphi access library your using, but if it
exposes cursors, then you could delete by position using the
cursor, but they are not always exposed.

Paul


Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com