asked
Marking Duplicates
Hi, I have table with duplicates like more than 1 are more than two, now i want to mark duplicate in new column, Example 'SN74ALVC164245DGG' this no has three row with same data i want to mark duplicate for the two 1 row will remain Thanks in advance
This is a very good way to locate and then remove duplicates
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/t-sql-tuesday-016-count-and-delete-duplicates [Edit] full answer: USE [adventureworks] GO IF OBJECT_ID('tempdb..#DupeTest') > 0 DROP TABLE #DupeTest CREATE TABLE #DupeTest ( ID INT IDENTITY ,-- column_name data_type,... OrderNo VARCHAR(50) , IsDupe CHAR(3) ) WHILE ( SELECT COUNT(*) FROM [dbo].[#DupeTest] ) < 100 BEGIN INSERT INTO [#DupeTest] ( [OrderNo] ) VALUES ( 'OrderNo' + CAST(( ABS(CHECKSUM(NEWID())) % 90 + 1 ) AS VARCHAR(2)) ) END ; SELECT * FROM [dbo].#DupeTest AS dt go WITH CTE_Dupes AS ( SELECT [dt].[ID] , [dt].[OrderNo] , ROW_NUMBER() OVER ( PARTITION BY [dt].[OrderNo] ORDER BY orderno ) AS r_n FROM [#DupeTest] AS dt ) UPDATE [#DupeTest] SET [IsDupe] = 'Yes' FROM [#DupeTest] AS dt INNER JOIN [CTE_Dupes] ON [dt].[ID] = [CTE_Dupes].[ID] WHERE [CTE_Dupes].[r_n] <> 1 UPDATE [#DupeTest] SET [IsDupe] = ISNULL([IsDupe], 'No') SELECT COUNT(*) as [how many] , [dt].[IsDupe] FROM [#DupeTest] AS dt GROUP BY [dt].[IsDupe]
so dont run the code to delete them, use it to mark them as you want.
You will need to update a column in the same table to mark some rows as duplicates or you will have to have a different table where you can store the PK of the duplicate rows. Do you have a column in the table or a 'duplicates' table that you can use? If not you will need to consider which is better to use
Sir, i created new column (duplicate) to mark original but if i update the column with the condition some duplicates also updated, i want to marke which is not duplicate in that new column it will update NO
