我有一个产品表希望包含重复的行,并且它的 uniq Id在其他6 个表中被引用。
我想删除产品表中以及依赖于我的产品的其他表中的这些重复行。
我考虑使用临时表来:
我有这个想法,但我不知道如何很好地实现它。
我不知道,临时表的选择是否正确。
感谢您的帮助和建议。
create table dbo.hasduplicates
(
id int identity,
--assume colA, colB is the entity/unique combo
colA varchar(10),
colB int,
someOtherColumn varchar(40)
);
insert into dbo.hasduplicates(colA, colB, someOtherColumn)
values
('A', 1, 'A1 - 1'),
('A', 1, 'A1 - 2'),
('A', 1, 'A1 - 3'),
--
('A', 2, 'A2 - 1'),
('A', 2, 'A2 - 2'),
--
('B', 1, 'B1 - 1'),
('B', 1, 'B1 - 2'),
('B', 1, 'B1 - 3');
select *
from dbo.hasduplicates;
--temp table holding the to-be-deleted ids (of the duplicates)
create table #ToBedeleted(IdToDelete int);
with dup
as
(
select *, row_number() over (partition by colA, colB /*<--cols of your entity go here*/ order by id) as RowNum
from dbo.hasduplicates
)
insert into #ToBedeleted(IdToDelete)
select Id
from dup
where RowNum >= 2;
--contains the ids for deletion
select * from #ToBedeleted;
--cleanup the referencing tables
/*
DELETE FROM dbo.Table1 WHERE Table1Id IN (SELECT IdToDelete FROM #ToBedeleted);
DELETE FROM dbo.Table2 WHERE Table2Id IN (SELECT IdToDelete FROM #ToBedeleted);
.............
DELETE FROM dbo.Table6 WHERE Table6Id IN (SELECT IdToDelete FROM #ToBedeleted);
--finally cleanup your products table
DELETE FROM dbo.hasduplicates WHERE Id IN (SELECT IdToDelete FROM #ToBedeleted);
*/
--/*
drop table #ToBedeleted;
drop table dbo.hasduplicates;
--*/
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句