How to delete duplicate rows without children in sql server

最新推荐文章于 2015-04-04 00:40:58 发布

原创最新推荐文章于 2015-04-04 00:40:58 发布 · 9.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#delete #sql server #table #statistics #null

SQL Server 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了一种方法，用于删除父表中没有子记录的重复行。通过创建临时表来筛选出重复的RRDIID，并确定哪些重复项具有子表中的关联记录。最终，该过程仅删除那些没有关联子记录的重复父表记录。

/*************written by Jiangong SUN**************/

I will introduce you how to delete unecessary duplicate rows in a parent table.

There are two tables:

1) Table DBO_Dealers, which is parent table, all rrdi code are unique

CREATE TABLE [dbo].[DBO_Dealers](
	[DealerId] [int] IDENTITY(1,1) NOT NULL,
	[RrdiId] [char](10) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [CSAQC_DBO_Dealers] PRIMARY KEY CLUSTERED 
(
	[DealerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2) Table DBO_Baskets, which is child table. It has a foreign key for table DBO_Dealers.

CREATE TABLE [dbo].[DBO_Baskets](

	[BasketId] [int] IDENTITY(1,1) NOT NULL,
	[FkDealer] [int] NULL,
 CONSTRAINT [CSAQC_DBO_Baskets] PRIMARY KEY CLUSTERED 
(
	[BasketId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO
ALTER TABLE [dbo].[DBO_Baskets]  WITH NOCHECK ADD  CONSTRAINT [CSAQI_DBO_Baskets_Dealers] FOREIGN KEY([FkDealer])
REFERENCES [dbo].[DBO_Dealers] ([DealerId])

The principle is delete duplicate rows in table DBO_Dealers who don't have children.

Let's go!

USE DATABASE;


IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'duplicateRRDITable'))
BEGIN
    drop table duplicateRRDITable;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'duplicateListTable'))
BEGIN
    drop table duplicateListTable;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dealershavecommands'))
BEGIN
    drop table dealershavecommands;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'finaltable'))
BEGIN
    drop table finaltable;
END


-- Get all duplicate rrdi code into table duplicateRRDITable
select rrdiid, count(*) as aaa
into duplicateRRDITable
from DBO_Dealers
group by rrdiid
having count(*) > 1;


-- Get all distinct rrdi from table duplicateRRDITable
select distinct DBO_Dealers.*
into duplicateListTable
from DBO_Dealers, duplicateRRDITable
where DBO_Dealers.rrdiid = duplicateRRDITable.rrdiid;


-- Get all rrdi who has commands in table duplicateListTable
select rrdiid, name, dealerid
into dealershavecommands
from duplicateListTable
right join DBO_Baskets on duplicateListTable.dealerid = DBO_Baskets.fkdealer
where dealerid is not null;


-- Get all rrdi who doesn't have commands
select * into finaltable
from (
select rrdiid, dealerid
from duplicateListTable 
except
select rrdiid, dealerid
from dealershavecommands
) t;


-- Delete all rrdi who doesn't have commands
delete DBO_Dealers
from DBO_Dealers
inner join finaltable
on DBO_Dealers.dealerId = finaltable.dealerId;


IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'duplicateRRDITable'))
BEGIN
    drop table duplicateRRDITable;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'duplicateListTable'))
BEGIN
    drop table duplicateListTable;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dealershavecommands'))
BEGIN
    drop table dealershavecommands;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'finaltable'))
BEGIN
    drop table finaltable;
END

Here it is!

Enjoy coding !

/*************written by garcon1986**************/