深圳智汇创想科技有限公司

-- allegro归档3个月前数据
    

-- 创建临时表存删除表的主联数据 （已经执行）
-- create table m_allegro_listing_sub_del_tmp1118
-- (
--   `Id` bigint NOT NULL ,
--   `CustomerId` int NOT NULL,
--   `OrderSourceId` int NOT NULL,
--   `OfferId` varchar(16) NOT NULL,
--     PRIMARY KEY (`Id`),
--      KEY `idx_CustomerId_OrderSourceId_OfferId` (`CustomerId`,`OrderSourceId`,`OfferId`)
-- );
--
-- INSERT m_allegro_listing_sub_del_tmp1118 (Id,CustomerID,OrderSourceID,OfferId)
-- select Id,CustomerID,OrderSourceID,OfferId from m_allegro_listing_sub where CustomerId=1 and  `Status` in ('INACTIVE','ACTIVATING','ENDED')  and LastModificationTime < '2022-08-18';
--



-- 清理m_allegro_listing表数据
DELETE
FROM m_allegro_listing
WHERE Id IN (SELECT t0.Id
             FROM m_allegro_listing AS t0
                      INNER JOIN m_allegro_listing_sub_del_tmp1118 AS t1
                                ON t0.CustomerId = t1.CustomerId AND t0.OrderSourceId = t1.OrderSourceId AND
                                   t0.OfferId = t1.OfferId);
                                                                    
-- 清理m_allegro_listing_edit表数据
DELETE
FROM m_allegro_listing_update
WHERE Id IN (SELECT t0.Id
             FROM m_allegro_listing_update AS t0
                      INNER JOIN m_allegro_listing_sub_del_tmp1118 AS t1
                                ON t0.CustomerId = t1.CustomerId AND t0.OrderSourceId = t1.OrderSourceId AND
                                   t0.OfferId = t1.OfferId);
                                                                    

-- 清理m_allegro_listing_image表数据
DELETE
FROM m_allegro_listing_image
WHERE Id IN (SELECT t0.Id
             FROM m_allegro_listing_image AS t0
                      INNER JOIN m_allegro_listing_sub_del_tmp1118 AS t1
                                ON t0.CustomerId = t1.CustomerId AND
                                   t0.OfferId = t1.OfferId);
                                                                    
                                                                    
-- 清理m_allegro_listing_attribute表数据
DELETE
FROM m_allegro_listing_attribute
WHERE Id IN (SELECT t0.Id
             FROM m_allegro_listing_attribute AS t0
                      INNER JOIN m_allegro_listing_sub_del_tmp1118 AS t1
                                ON t0.CustomerId = t1.CustomerId AND t0.OrderSourceId = t1.OrderSourceId AND
                                   t0.OfferId = t1.OfferId);
                                                                    


-- 清理m_allegro_listing_sub_additional表数据
DELETE
FROM m_allegro_listing_sub_additional
WHERE Id IN (SELECT t0.Id
             FROM m_allegro_listing_sub_additional AS t0
                      INNER JOIN m_allegro_listing_sub_del_tmp1118 AS t1
                                ON t0.CustomerId = t1.CustomerId AND t0.OrderSourceId = t1.OrderSourceId AND
                                   t0.OfferId = t1.OfferId);                                                                                                                                         


-- 清理m_allegro_listing_sub表数据
delete from m_allegro_listing_sub where id in (
select ID from m_allegro_listing_sub_del_tmp1118
);