I currently have a table called People. Within this table there are thousands of rows of data which follow the below layout:
gkey | Name | Date | Person_Id
1 | Fred | 12/05/2012 | ABC123456
2 | John | 12/05/2012 | DEF123456
3 | Dave | 12/05/2012 | GHI123456
4 | Fred | 12/05/2012 | JKL123456
5 | Leno | 12/05/2012 | ABC123456
If I execute the following:
SELECT [PERSON_ID], COUNT(*) TotalCount
FROM [Database].[dbo].[People]
GROUP BY [PERSON_ID]
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
I get a return of:
Person_Id | TotalCount
ABC123456 | 2
Now I would like to remove just one row of the duplicate values so when I execute the above query I return no results. Is this possible?
So to delete the duplicate record with SQL Server we can use the SET ROWCOUNT command to limit the number of rows affected by a query. By setting it to 1 we can just delete one of these rows in the table. Note: the select commands are just used to show the data prior and after the delete occurs.
The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.
WITH a as
(
SELECT row_number() over (partition by [PERSON_ID] order by name) rn
FROM [Database].[dbo].[People]
)
DELETE FROM a
WHERE rn = 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With