Thanks, will try your suggestion. RBS
On 8/21/12, Ann Harrison <[email protected]> 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] > >
