Skip to content

ErcanOPAK.com

  • ASP.Net WebForms
  • ASP.Net MVC
  • C#
  • SQL
  • MySQL
  • PHP
  • Devexpress
  • Reportviewer
  • About
SQL

How to find repetitive records and keep only 1 record for uniqueness in SQL

- 08.11.19 | 22.11.19 - ErcanOPAK
--STEP 1: Find Repetitive Records (Only the Active Ones)
SELECT INDEX1, INDEX2
INTO #TEMP_TABLE
FROM MY_TABLE
WHERE ACTIVE = 1
GROUP BY INDEX1, INDEX2
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC

--STEP 2: Create the Cursor
DECLARE @INDEX1 INT
DECLARE @INDEX2 INT
DECLARE @ID INT
DECLARE MY_CURSOR CURSOR FOR
                                                                               
SELECT INDEX1, INDEX2 FROM #TEMP_TABLE

OPEN MY_CURSOR

--Be careful about the order of indexes
FETCH NEXT FROM MY_CURSOR INTO @INDEX1, @INDEX2

WHILE @@FETCH_STATUS = 0
                BEGIN
                
                               --STEP 3: Choose an active record from the repetitive ones
                               SELECT TOP(1) @ID = ID FROM MY_TABLE
                               WHERE INDEX1 = @INDEX1 AND INDEX2 = @INDEX2 AND ACTIVE = 1

                               --STEP 4: Instead of deleting a record, I always choose to update it as Inactive
                               UPDATE MY_TABLE
             SET ACTIVE = 0
                               WHERE INDEX1 = @INDEX1 AND INDEX2 = @INDEX2 AND ID != @ID

                               PRINT 'The repetitive records have been deleted.'
                               FETCH NEXT FROM MY_CURSOR INTO @INDEX1, @INDEX2

                END

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

DROP TABLE #TEMP_TABLE

Related posts:

DATEADD Function in SQL
How to check for 'IS NOT NULL' And 'IS NOT EMPTY' string in SQL
How to hide letters with asterisks except first letter in SQL
Create, Alter, Drop and Execute SQL Server Stored Procedures
Search text in all stored procedures in SQL Server
Post Views: 75

Post navigation

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
How to override a CSS Property

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

May 2022
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
« Apr    

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (331)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (220)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (190)
  • Find numbers with more than two decimal places in SQL (190)
  • Confirm before process with ASPxButton in Devexpress (189)
  • ASPxGridView – Disable CheckBox based on condition in GridViewCommandColumn (189)
  • How to make some specific word(s) Bold or Underline in ReportViewer (189)
  • Devexpress ASPxGridview Column Grouping in Code (176)
  • Add Constraint to SQL Table to ensure email contains @ (174)
  • Tense Changes When Using Reported Speech (167)

Recent Posts

  • How to put text inside MVC Razor code block
  • How to solve 0xc0000135 application errors after Windows 11 Update
  • How to get the integrity value for a jquery version of script reference in a web page
  • How to get session value in Javascript
  • How to get value from resx file in C#
  • How to lock the ciritical code part in C#
  • How to make theater mode the default for Youtube
  • How to turn off YouTube annotations and cards
  • How to hide Youtube chat windows permanently
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server

Recent Posts

  • How to put text inside MVC Razor code block
  • How to solve 0xc0000135 application errors after Windows 11 Update
  • How to get the integrity value for a jquery version of script reference in a web page
  • How to get session value in Javascript
  • How to get value from resx file in C#

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (331)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (220)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (190)
  • Find numbers with more than two decimal places in SQL (190)
  • Confirm before process with ASPxButton in Devexpress (189)

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter

powered by XBlog Plus WordPress Theme