|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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! |
|
#2
|
|||
|
|||
|
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>> |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Delete duplicates - T/SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|