diagbotcloud/docs/008.20190410标签别名脏数据处理/标签别名脏数据处理.sql

use `sys-icss`;
update icss_retrieval_mapping a join
(SELECT
a.id as oldId,
b.id as newId,
a.name
FROM
(SELECT
id,name
FROM icss_retrieval
WHERE name in
(SELECT  
name
FROM `icss_retrieval`
group by name
HAVING count(1)>1)) a
JOIN
(SELECT
id,name
FROM(
SELECT
id,name
FROM icss_retrieval
WHERE name in
(SELECT  
name
FROM `icss_retrieval`
group by name
HAVING count(1)>1)
) t
group by name) b on a.name=b.name) b on a.retrieval_id=b.oldId
set a.retrieval_id=b.newId;

update icss_retrieval a left join icss_retrieval_mapping b on a.id=b.retrieval_id set a.is_deleted='Y' where b.id is null;
update icss_retrieval_mapping a left join icss_retrieval b on a.retrieval_id=b.id set a.is_deleted='Y' where b.id is  null;
delete from icss_retrieval_mapping where is_deleted='Y';
delete from icss_retrieval where is_deleted='Y';