Re: Table With Duplicate Records..

From: <tjmxyz_at_my-deja.com>
Date: Thu, 05 Aug 1999 08:21:45 GMT
Message-ID: <7obhii$t1q$1@nnrp1.deja.com>

create table new_table as select distinct ACCOUNT_NUM, BRANCH_ID, PTYPE from bcis_mf_monthly;

However it will take only the first occurence in the set of duplicates (I think)

You might consider a constraint which can be easlily made with

create unique index idxname on bcis_mf_monthly (CCOUNT_NUM, BRANCH_ID,PTYPE); This will ensure no more such duplicates come in.

You will then see an error message where in your application these are being created and you can fix this there...

I don't understand exactly if you say:
CCOUNT_NUM, BRANCH_ID,PTYPE make each field unique then your query did not necessarily show this.

In the belwo query you should replace
hh_household_id
with CCOUNT_NUM, BRANCH_ID,PTYPE as far as I understand.

select count(*) total, hh_household_id from bcis_mf_monthly having count(hh_household_id) > 1
group by hh_household_id;

Good luck!

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 05 1999 - 03:21:45 CDT