Corrupt Category / Product

Corrupt Category

use ATGPUBLISHING

-- Declare the variable with category Id
declare @categoryid varchar(40)
set @categoryid = 'cat5540038'

-- Create tmp table
CREATE TABLE #dcs_cat_chldprd_tmp
(
[sec_asset_version] [numeric](19, 0) NOT NULL,
       [asset_version] [numeric](19, 0) NOT NULL,
       [category_id] [varchar](40) NOT NULL,
       [sequence_num] [int] NOT NULL,
       [child_prd_id] [varchar](40) NOT NULL,
       [new_sequence_num] [int] NOT NULL
)


-- Delete not is_hed version of category
delete from ATGPUBLISHING.dbo.dcs_cat_chldprd where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

-- Delete not is_head version of products
delete t1
FROM ATGPUBLISHING.dbo.dcs_cat_chldprd t1
inner join ATGPUBLISHING.dbo.dcs_product t2 on t2.asset_version = t1.sec_asset_version and t2.product_id=t1.child_prd_id
where t1.category_id=@categoryid
and t1.asset_version=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)
and t2.is_head !=1

-- Inserting data to tmp table to resequence the child products
INSERT INTO #dcs_cat_chldprd_tmp
(sec_asset_version, asset_version, category_id, sequence_num, child_prd_id, new_sequence_num)
select *
,ROW_NUMBER() OVER (
    order by category_id asc) - 1 AS 'new_sequence_num'
from ATGPUBLISHING.dbo.dcs_cat_chldprd
where category_id=@categoryid
order by child_prd_id

-- Cleaning child product table
delete from ATGPUBLISHING.dbo.dcs_cat_chldprd
where category_id=@categoryid


-- Copying data from tmp table to child product table
insert into dcs_cat_chldprd (sec_asset_version,asset_version,category_id,sequence_num,child_prd_id)  (
       select sec_asset_version,asset_version,category_id,new_sequence_num,child_prd_id from #dcs_cat_chldprd_tmp
);

-- Delete not is_hed version of category
delete from ATGPUBLISHING.dbo.dcs_category_sites where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

delete from ATGPUBLISHING.dbo.dcs_cat_catalogs where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

delete from ATGPUBLISHING.dbo.dcs_cat_chldcat where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

delete from ATGPUBLISHING.dbo.dcs_cat_prnt_cats where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

delete from ATGPUBLISHING.dbo.dcs_cat_anc_cats where category_id=@categoryid and asset_version !=
(select asset_version from dcs_category where category_id=@categoryid and is_head=1)

-- Drop tmp Table
drop table #dcs_cat_chldprd_tmp