Duplicate keys in a table (select)

Mark Gebbie  writes:

>We have a table that has managed to get duplicate keys inserted into it. Is 
>there any sort of elegant isql select that can be done to identify which keys
>are duplicated? Or any other method? There are 2 columns in the key.

Find the tuples which have the same "key" but different TIDs:

select a, b, tid
  from t t1
 where exists (select 1
                 from t t2
                where t1.a = t2.a and
                      t1.b = t2.b and
                      t1.tid != t2.tid)

You can then delete the duplicates using the TID, rather than a and b!

I am sure that someone can think of a quicker solution, but this will 
certainly work.

-- 
Graham J Bolton    | Work: Tel: 00 31 10 2732233 | Einstein said: "Everything
Elegant Relational |       Fax: 00 31 10 2731114 | should be as simple as
  Development bv   |                             | possible, but no simpler"
Schiedam, Holland  | Home: Tel: 00 31 10 4736272 | 'Inside Risks', CACM
Ingres Q & A
To William's Home Page
Email William