Many a times when we update database, there are chances that we may insert duplicates. It is a headache to maintain such database if the source is not within our control.
Below is a way to solve the headache.
with Uniqrow as (
select *,ROW_NUMBER() over (partition by Case_ID order by case_id,x_Cust_Track_No DESC) as rownumb FROM [wfm_ods].[dbo].[TABLE_CASE]
)
delete from Uniqrow where rownumb > 1
The above is based on table_case in WFMODS database. It somehow retrieved duplicate records and there is no way to find out why. Its function is to simply based on the grouping of case_id and rank it. Since we can't just do a delete directly, we use a CTE "with clause" to encase the ranking statement.
The result is very fast. removing 2700 case in less than 1 sec from a total of 11000 cases.
No comments:
Post a Comment