Bart,

> Firebird 1.5, classic on Windows.
>
> Have a table with this structure:
>
> CREATE TABLE KEYWORD(
> TERM_KEY CHAR(10) NOT NULL,
> TERM_ID CHAR(5) NOT NULL)
>
>

> Now I am trying to run a SQL to delete duplicate records:
>
> delete from keyword k where
> not k.rdb$db_key in (select
> max(k2.rdb$db_key)
> from
> keyword k2
> group by
> k2.term_key,
> k2.term_id)
>
> It runs, but no duplicate records are deleted.
> The select statement in the above is fine, so it correctly produces
> unique records.
> Why does the delete SQL not work?
>

Hmm... beats me, I would expect that to delete all but the record with
the highest
db_key in the database because your not doing anything to match the K
records with
the K2 records.

This might be better.

delete from keyword k
where k.rdb$db_key <> (select max (k2.rdb$db_key) from keyword k2
where k2.term_key =
k.term_key and k2.term_id = k.term_id)


There's nothing wrong with referencing the rdb$db_key. It's intended for
application use. Just
don't try to modify it, and if using it from a program be aware that its
length varies. It's fixed for
tables but doubles with each stream in a view.

Good luck,

Ann


[Non-text portions of this message have been removed]