in db2, the the dba is able to easily remove duplicates with this type of syntax:

 DELETE FROM
   (
select ROWNUMBER() OVER (PARTITION  BY
   activity_id ,
    tour_point_id ,
    etc etc etc
    )
    AS RN
     FROM  dstage.activity_audit
      where
      updated_by = 'wlsedi'
and update_date > '2014-08-01 00:00:00.000'
             )
       AS A
WHERE RN > 1;



is there an equivalent in Informix?

thanks
tom

To find them have a look at 
start violations table for yourtable.....

the create the unique index in filterring mode will place the duplicates 
in the yourtable_vio table.
This can be used to decide what to kick out and what to keep.

hmmm you need to check the manuals though it is a long time ago since i used 
this.


Superboer.


Op woensdag 10 september 2014 22:06:33 UTC+2 schreef Jack Parker:
> If they are true duplicates, then you cannot filter to get only one.  You have to select distinct into a temp table, delete the original data, then insert your now unique data.  If they are not true duplicates, then you can filter on the identifying difference and use that to delete.  delete from table where filter_condition.
> 
> j.
> 
> On Sep 10, 2014, at 3:55 PM, tomcaml@gmail.com wrote:
> 
> > in db2, the the dba is able to easily remove duplicates with this type of syntax:
> > 
> > DELETE FROM
> >   (
> > select ROWNUMBER() OVER (PARTITION  BY
> >   activity_id ,
> >    tour_point_id ,
> >    etc etc etc
> >    )
> >    AS RN
> >     FROM  dstage.activity_audit
> >      where
> >      updated_by = 'wlsedi'
> > and update_date > '2014-08-01 00:00:00.000'
> >             )
> >       AS A
> > WHERE RN > 1;
> > 
> > 
> > 
> > is there an equivalent in Informix?
> > 
> > thanks
> > tom
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list

If they are true duplicates, then you cannot filter to get only one.  You have to select distinct into a temp table, delete the original data, then insert your now unique data.  If they are not true duplicates, then you can filter on the identifying difference and use that to delete.  delete from table where filter_condition.

j.

On Sep 10, 2014, at 3:55 PM, tomcaml@gmail.com wrote:

> in db2, the the dba is able to easily remove duplicates with this type of syntax:
> 
> DELETE FROM
>   (
> select ROWNUMBER() OVER (PARTITION  BY
>   activity_id ,
>    tour_point_id ,
>    etc etc etc
>    )
>    AS RN
>     FROM  dstage.activity_audit
>      where
>      updated_by = 'wlsedi'
> and update_date > '2014-08-01 00:00:00.000'
>             )
>       AS A
> WHERE RN > 1;
> 
> 
> 
> is there an equivalent in Informix?
> 
> thanks
> tom
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list