TNPM/duplicates

From neil.tappsville.com
Revision as of 09:48, 3 September 2019 by Gonzo (talk | contribs) (Created page with "Alcatel-Lucent 5620 SAM Tech pack used to 'break the Proviso model', that is the UBA/SAMIF should ensure that it doesnt insert any changes about the same object at less than a...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

Alcatel-Lucent 5620 SAM Tech pack used to 'break the Proviso model', that is the UBA/SAMIF should ensure that it doesnt insert any changes about the same object at less than a second interval. The database has no checks on inserts.

Find duplicates

select count(*)
from (select idx_metric, idx_resource, dte_date, count(*) as row_count
from prop_desc_hist
where str_hist_action in ('I', 'U')
and dte_date > 1288565047
group by idx_metric, idx_resource, dte_date having count(*) > 1 )


deletes all but the latest entry

delete
from prop_desc_hist a
where a.str_hist_action in ('I','U')
and rowid <> ( select max(rowid)
from prop_desc_hist b
WHERE A.idx_metric=B.idx_metric
and A.idx_resource=B.idx_resource
and A.dte_hist_date=B.dte_hist_date
and A.STR_HIST_ACTION in ('I','U') );