Purpose of Query
This query deletes the duplicate versions created in the MNGT base.
Applicable CAST Version
|Microsoft SQL Server|
Query for CSS
with DuplicatedVersions as ( select cpv.object_name, max(cpv.object_id) as Max from cms_portf_version cpv join cms_portf_application cpa on cpa.object_id = cpv.application_id where cpa.object_name = '<app_name>' group by cpv.object_name having count(1) > 1 ), ToDelete as( select cpv.object_id from cms_portf_version cpv join cms_portf_application cpa on cpa.object_id = cpv.application_id join DuplicatedVersions dv on dv.object_name = cpv.object_name and cpa.object_name = '<app_name>' and dv.Max != cpv.object_id ) delete from cms_portf_version where object_id in( select object_id from ToDelete )
Query result interpretation
The query deletes all the duplicate versions in CAST-MS