Duplicate Records 
Author Message
 Duplicate Records

Make a unique index for the key: (see the manual for references)
here is an example I used

-- step: 1
--  drop index "oasisadm".pract_efec_ben04 ;

-- step: 2
--   create unique index "oasisadm".pract_efec_ben04 on
"oasisadm".pract_efec_benef
--     (cod_financ,titular,dep,nro_ordem,cod_pract) in imedprod03
--    DISABLED
--    ;

-- step: 3
--  start violations table for pract_efec_benef using pp1, pp2 max rows 500
;
--  set indexes "oasisadm".pract_efec_ben04 enabled ;

-- step: 4
-- delete from pp1;  -- delete before creating the index just in case
--  set indexes "oasisadm".pract_efec_ben04 enabled ;

-- step: 5
-- filter with pp1 and use original table to find the id's

-- step 6
-- delete duplicates from original table
-- delete from pract_efec_benef where rowid in (select id from  losid);

-- step 7
-- create unique index

may have to repeat the procedure a couple of times to be sure
yours

Quote:
>  N. Daniel Bonesana
> SIEMENS-ITRON BUSINESS SERVICES S.A.
> Balcarce 683, (C1064AAM) Buenos Aires, Argentina.

-----Original Message-----

Sent: Monday, November 27, 2000 12:19 PM
To: List Informix (E-mail)
Subject: Duplicate Records

Help...

Is there an easy way to delete duplicate records within a table.
I have a table that has item, vendor and last purchase date and cost.
If we happen to buy that item from two vendors it creates two records.
I am trying to consolidate the item cost into a single table by
item number eliminating the vendor part. Any ideals on how to do this
easily?

Thanks

Libi



Wed, 18 Jun 1902 08:00:00 GMT
 Duplicate Records

insert into new_table select unique <list_of_fields_except_vendor> from
old_table

beware of long transaction and lock overflow.

Quote:
> -----Mensaje original-----

> Enviado el:        Lunes 27 de Noviembre de 2000 10:19 AM
> Para:      List Informix (E-mail)
> Asunto:    Duplicate Records

> Help...

> Is there an easy way to delete duplicate records within a table.
> I have a table that has item, vendor and last purchase date and cost.
> If we happen to buy that item from two vendors it creates two records.
> I am trying to consolidate the item cost into a single table by
> item number eliminating the vendor part. Any ideals on how to do this
> easily?

> Thanks

> Libi



Wed, 18 Jun 1902 08:00:00 GMT
 Duplicate Records

How big a table/How many dupes?  If you have a lot, then unload and reload.
If you have a few then...

select key(s) from table group by 1 having count(*) > 1 into temp t1 with no
log;
update statistics for table t1;
select unique a.* from table a, t1 where a.key(s)=t1.key(s) into temp t2
with no log;
delete from table where exists (select 0 from t1 where
table.key(s)=t1.key(s));

(actually more efficient to do:
        unload to file.sql
        select "delete from table where key(s) = " || key(s) || ";" from t1
        dbaccess database file.sql - unless you have XPS in which case use
the delete join)

insert into table select * from t2;  (get back your new non-dupes)

Yes I'm playing fast and loose - you should set DBDELIMTIER="" so that you
don't get a pipe in the unload, or you will have to sed (or whatnot) out the
pipe from file.sql.

Depending on how many rows affected and how big the table is and how many
columns are in your duplicate key, you may not want to do the group by
operation as stated.

You may want to get just the rowid (if you have them) out and delete the
highest of those two instead of removing all the data and putting some back
in like I suggested.

It really depends on the shape and bladder control of your cat as to how you
want to skin it.

As always check the counts carefully throughout all steps, make sure you
know how many rows you are going to wipe, how many you actually wiped and be
prepared to rebuild should you wipe too many.

cheers
j.

Quote:
> -----Original Message-----

> Sent: Monday, November 27, 2000 10:19 AM
> To: List Informix (E-mail)
> Subject: Duplicate Records

> Help...

> Is there an easy way to delete duplicate records within a table.
> I have a table that has item, vendor and last purchase date and cost.
> If we happen to buy that item from two vendors it creates two records.
> I am trying to consolidate the item cost into a single table by
> item number eliminating the vendor part. Any ideals on how to do this
> easily?

> Thanks

> Libi



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. I am getting duplicate records, I mean everything is duplicate even Access record number

2. Fix Duplicate fields Rather then Duplicate Records?

3. correction :select duplicate and non duplicate record

4. select duplicate and non duplicate records

5. sorting records alphabetically and deleting duplicate records

6. Duplicate records

7. deleting duplicates records

8. duplicate records

9. Duplicate records in stored procedure resutltset with DISTINCT.

10. Duplicate records

11. Duplicate records

12. deleting duplicate records


 
Powered by phpBB® Forum Software