Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old March 17th, 2005, 03:35 AM
Paula Paula is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 65 Paula User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 37 m 32 sec
Reputation Power: 5
Delete duplicates - T/SQL

Hi,
Can anyone help please? I have some code which gets a list of duplicate records. I want to be able to delete one of the records and leave the other. However so far my code deletes all records. Can anyone tell me how I select just one of each pair to delete? It doesn't matter which one of the pair goes.

delete
FROM Sickness
WHERE (staff_id IN
(SELECT [staff_id]
FROM [Sickness] AS Tmp
GROUP BY [staff_id], [start_date], [end_date]
HAVING COUNT(*) > 1 AND
[start_date] = [Sickness].[start_date] AND
[end_date] = [Sickness].[end_date]))

Thank you!

Reply With Quote
  #2  
Old March 17th, 2005, 04:49 AM
Paula Paula is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 65 Paula User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 37 m 32 sec
Reputation Power: 5
Here is the solution to the above problem, if anyone is interesting.

/* Script for deletion of Duplicate record from the Sickness Table */

<<Declare @payroll varchar (8),
@staff_id int,
@start_date datetime,
@end_date datetime,
@sickness_code int,
@cnt int

Declare getallrecords cursor local static For
Select count (1), payroll, staff_id, start_date, end_date, sickness_code
from sickness (nolock)
group by payroll, staff_id, start_date, end_date, sickness_code having count(1)>1


Open getallrecords

Fetch next from getallrecords into @cnt,@payroll,@staff_id,@start_date, @end_date,
@sickness_code

--Cursor to check with all other records

While @@fetch_status=0
Begin
Set @cnt= @cnt-1
Set rowcount @cnt

-- Deleting the duplicate records. Observe that all fields are mentioned at the where condition

Delete from sickness where payroll = @payroll and staff_id = @staff_id
and start_date = @start_date and end_date = @end_date and sickness_code = @sickness_code

Set rowcount 0

Fetch next from getallrecords into @cnt,@payroll,@staff_id,@start_date, @end_date,
@sickness_code
End

Close getallrecords
Deallocate getallrecords>>

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseMicrosoft SQL Server > Delete duplicates - T/SQL


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway
Stay green...Green IT