RE: 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 wheretable.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.
> -----Original Message-----
> From: Libi Maniace [mailto:lmaniace@beissd.com]
> 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
>