Thanks, will try your suggestion.

RBS

On 8/21/12, Ann Harrison <aharrison@...> wrote:
> 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]
>
>