现在表T1 中有很多重复的数据。表T2,T3 有引用T1表的id字段。
如何用sql或存储过程删除没有被T2和T3引用的T1表的重复数据。。(T1中根据unid来判断是否重复)
表结构下,T2,T3 的recId是 T1的id
T1
id name unid
T2
id p1 recId
T2
id p2 recId
-------查询T1中重复的数据
select * from(
select unid,count(unid) con,max(id) from T1 group by unid
) where con>1
...全文
712打赏收藏
删除没被引用的重复数据
现在表T1 中有很多重复的数据。表T2,T3 有引用T1表的id字段。 如何用sql或存储过程删除没有被T2和T3引用的T1表的重复数据。。(T1中根据unid来判断是否重复) 表结构下,T2,T3 的recId是 T1的id T1 id name unid T2 id p1 recId T2 id p2 recId -------查询T1中重复的数据 select * from( select unid,count(unid) con,max(id) from T1 group by unid
DELETE FROM T1
WHERE unid IN
select DISTINCT unid from T1
where T1.id NOT IN (
SELECT T2.recId FROM T2
UNION
SELECT T3.recId FROM T3
)
AND rowid!=(select max(A.rowid) from T1 A WHERE A.unid=T1.uni))
查询出满足删除的重复数据:
select id, max(rowid) max_rowid from T1 where not exists (select '*' from T2 where recId = id union all select '*' from T3 where recId = id) group by unid having count(unid)>1
删除对应的ID
delete from T1 where rowid !=max_rowid and id = id(上面查询出来的)