removing duplicates

[t***@gmail.com]

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

[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.

Post by t***@gmail.com
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
http://www.iiug.org/mailman/listinfo/informix-list

[s***@t-online.de]

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.

Post by 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.

Post by t***@gmail.com
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
http://www.iiug.org/mailman/listinfo/informix-list