Re: isolating primary keys from other distinct fields

Kevi Probably this does not solve your problem, but dont you think that you should not allow users to enter information more than once? You can do this by defining a unique index on username. Of course this will still not ensure uniqueness if these are real user names, since I could enter my information twice, once as "S Pal" and another as "Sujit Pal", but if these are like login names, then you are pretty much covered by the index. To figure out the existing duplicate usernames, you can run the following query which you will find in the text for error # 371 - cannot create unique index on column with duplicate data: SELECT username FROM myusertable main WHERE 1 < ( SELECT COUNT(*) FROM myusertable sub WHERE main.username="sub.username" ); Then you would need to go deleting records you found, as well as fix any referential integrity violations that are explicitly declared in the database or implicit in the application. HTH Sujit vapourfrost1@my-dejanews.com on 04/08/99 08:51:55 AM Please respond to vapourfrost1@my-dejanews.com To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: isolating primary keys from other distinct fields I am using an Informix SE Database to catalog user names. I have duplicate entries for people that entered information more than once but each entry is listed by it's unique primary key. I have been trying to isolate the primary key by doing a "select distinct" across the other fields and placing the information in a temp table to have the permanent table check against. But if I select the distinct entries and iunclude the primary key it returns every entry since every primary key is distinct. If I do not include the primary key in the query that field is not placed in the temp table and I cannot check against the primary keys. I also tried a subquery but cannot seem to use a select distinct in the subquery. Can anyone out there help me? Kevi -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own