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