/*************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**************/