Deleting dupicates using sql2000 server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting dupicates using sql2000 server

Author

Topic

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-07-09 : 00:17:13

Can any one show me how to delete duplicates in the below table using SQL server 2000
I know how to do it using sql Server 2005
Declare @t table
(
id int,
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden'

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-09 : 21:31:37

[code]
Declare @t table
(
id int,
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden' union all
SELECT 1, 'Brake Pads', 'Holden' -- Added a true duplicate

Select * From @t Order By id

-- Only deletes semi duplicates (ie. those that have same col1 & col2 but a different id
Delete Z
From @t Z
Inner Join
(
Select A.id, A.Col1, A.Col2, minId = MIN(B.Id)
From @t A
Inner Join @t B
On A.Col1 = B.Col1
and A.Col2 = B.Col2
Group By A.id, A.Col1, A.Col2
) Y
On Z.id = Y.id
and Y.id <> Y.minId

Select * From @t Order By id

-- deletes semi & true duplicates (ie. those that have same col1 & col2 and even same id)
Set RowCount 1 -- Limits results to a single record

Select Col1, Col2, Cnt = COUNT(*) From @t Group By Col1, Col2 Having COUNT(*)>1
While @@ROWCOUNT > 0
Delete A
From @t A
Inner Join
(
Select Col1, Col2
From @t
Group By Col1, Col2
Having COUNT(*)>1
) B
On A.Col1 = B.Col1
and A.Col2 = B.Col2

Set RowCount 0 -- Resets result limiter... don't forget this line!

Select * From @t Order By id
[/code]

Corey

I Has Returned!!

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-07-10 : 00:29:31

Thanks a lot , much appreciated. I will try out soon. I had been racking my brains to find a way, but this beats me.
Glen

quote:

Originally posted by Seventhnight


Declare @t table
(
id int, 
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all 
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden' union all
SELECT 1, 'Brake Pads', 'Holden' -- Added a true duplicate

Select * From @t Order By id

-- Only deletes semi duplicates (ie. those that have same col1 & col2 but a different id
Delete Z 
From @t Z
Inner Join
	(
	Select A.id, A.Col1, A.Col2, minId = MIN(B.Id) 
	From @t A
	Inner Join @t B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2
	Group By A.id, A.Col1, A.Col2
	) Y
On Z.id = Y.id
and Y.id <> Y.minId

Select * From @t Order By id

-- deletes semi & true duplicates (ie. those that have same col1 & col2 and even same id)
Set RowCount 1 -- Limits results to a single record

Select Col1, Col2, Cnt = COUNT(*) From @t Group By Col1, Col2 Having COUNT(*)>1
While @@ROWCOUNT > 0
	Delete A
	From @t A 
	Inner Join 
		(
		Select Col1, Col2
		From @t 
		Group By Col1, Col2 
		Having COUNT(*)>1
		) B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2

Set RowCount 0 -- Resets result limiter... don't forget this line!

Select * From @t Order By id

Corey

I Has Returned!!

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-07-10 : 02:11:53

[quote]Originally posted by glendcruz

Thanks a lot , much appreciated. I will try out soon. I had been racking my brains to find a way, but this beats me.
Glen

I tried your example out just now, but I am not getting the desired results.
I cannot understand what this means
EG:
On Z.id = Y.id
and Y.id <> Y.minId

and also in the while loop

Having COUNT(*)>1

While @@ROWCOUNT > 0
Group By Col1, Col2
Having COUNT(*)>1
-----------------------------------

[quote]Originally posted by Seventhnight

[code]
Declare @t table
(
id int,
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden' union all
SELECT 1, 'Brake Pads', 'Holden' -- Added a true duplicate

Select * From @t Order By id

-- Only deletes semi duplicates (ie. those that have same col1 & col2 but a different id
Delete Z
From @t Z
Inner Join
(
Select A.id, A.Col1, A.Col2, minId = MIN(B.Id)
From @t A
Inner Join @t B
On A.Col1 = B.Col1
and A.Col2 = B.Col2
Group By A.id, A.Col1, A.Col2
) Y
On Z.id = Y.id
and Y.id <> Y.minId

Select * From @t Order By id
-- deletes semi & true duplicates (ie. those that have same col1 & col2 and even same id)
Set RowCount 1 -- Limits results to a single record

Select Col1, Col2, Cnt = COUNT(*) From @t Group By Col1, Col2 Having COUNT(*)>1
While @@ROWCOUNT > 0
Delete A
From @t A
Inner Join
(
Select Col1, Col2
From @t
Group By Col1, Col2
Having COUNT(*)>1
) B
On A.Col1 = B.Col1
and A.Col2 = B.Col2

Set RowCount 0 -- Resets result limiter... don't forget this line!

Select * From @t Order By id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-10 : 21:24:08

What is not as expected??

Added some more comments:

quote:

Originally posted by Seventhnight


Declare @t table
(
id int, 
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all 
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden' union all
SELECT 1, 'Brake Pads', 'Holden' -- Added a true duplicate

Select * From @t Order By id

-- Only deletes semi duplicates (ie. those that have same col1 & col2 but a different id
Delete Z 
From @t Z
Inner Join
	(
	-- Group by id, Col1, Col2 and determine the minimum Id with matching Col1, Col2
	Select A.id, A.Col1, A.Col2, minId = MIN(B.Id) 
	From @t A
	Inner Join @t B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2
	Group By A.id, A.Col1, A.Col2 
	) Y
On Z.id = Y.id -- Join each record back to itself (can't do a group by in the same level as a delete)
and Y.id <> Y.minId -- exclude the minimum Id as it is the original

Select * From @t Order By id

-- deletes semi & true duplicates (ie. those that have same col1 & col2 and even same id)
Set RowCount 1 -- Limits results to a single record

-- Having count(*)>1 will limit the result to only Col1, Col2 pairs that have duplicates
Select Col1, Col2, Cnt = COUNT(*) From @t Group By Col1, Col2 Having COUNT(*)>1

-- Loop until no records are affected
While @@ROWCOUNT > 0
Begin
	Delete A
	From @t A 
	Inner Join 
		(
		Select Col1, Col2
		From @t 
		Group By Col1, Col2 
		Having COUNT(*)>1
		) B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2
End

Set RowCount 0 -- Resets result limiter... don't forget this line!

Select * From @t Order By id

Corey

I Has Returned!!

Corey

I Has Returned!!

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-07-11 : 19:57:06

Thanks Seventhnight, it runs perfect.
Glen

quote:

Originally posted by Seventhnight

What is not as expected??

Added some more comments:

quote:

Originally posted by Seventhnight


Declare @t table
(
id int, 
Col1 varchar(12),
Col2 varchar(15)
)
insert into @t
select 1, 'Brake Pads', 'Holden' union all
select 2, 'Clutch', 'Ford' union all 
select 3, 'Bulbs', 'Honda' union aLL
SELECT 4, 'Brake Pads', 'Holden' union all
SELECT 1, 'Brake Pads', 'Holden' -- Added a true duplicate

Select * From @t Order By id

-- Only deletes semi duplicates (ie. those that have same col1 & col2 but a different id
Delete Z 
From @t Z
Inner Join
	(
	-- Group by id, Col1, Col2 and determine the minimum Id with matching Col1, Col2
	Select A.id, A.Col1, A.Col2, minId = MIN(B.Id) 
	From @t A
	Inner Join @t B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2
	Group By A.id, A.Col1, A.Col2 
	) Y
On Z.id = Y.id -- Join each record back to itself (can't do a group by in the same level as a delete)
and Y.id <> Y.minId -- exclude the minimum Id as it is the original

Select * From @t Order By id

-- deletes semi & true duplicates (ie. those that have same col1 & col2 and even same id)
Set RowCount 1 -- Limits results to a single record

-- Having count(*)>1 will limit the result to only Col1, Col2 pairs that have duplicates
Select Col1, Col2, Cnt = COUNT(*) From @t Group By Col1, Col2 Having COUNT(*)>1

-- Loop until no records are affected
While @@ROWCOUNT > 0
Begin
	Delete A
	From @t A 
	Inner Join 
		(
		Select Col1, Col2
		From @t 
		Group By Col1, Col2 
		Having COUNT(*)>1
		) B
	On A.Col1 = B.Col1
	and A.Col2 = B.Col2
End

Set RowCount 0 -- Resets result limiter... don't forget this line!

Select * From @t Order By id

Corey

I Has Returned!!

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources