AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
![]() View Student data after duplicates have been removedĬreating Duplicates View and RemoveDuplicates Stored Procedure Let us quickly check the records after we have removed the duplicates: USE UniversityV2 Removing duplicates by using Self-Referencing method We can remove the duplicates using the same method we used to find duplicates with the exception of using DELETE in line with its syntax as follows: USE UniversityV2 Removing Duplicates by Self-referencing Method The output of the above script shows us only the duplicate records: Finding Duplicates by Self-referencing Method-2Īnother way to find duplicates using self-referencing is to use INNER JOIN as follows: - Self-Referencing method 2 to find duplicate students having same name, course, marks, exam date ,S1.ExamDate as S1_ExamDate, S2.ExamDate AS S2_ExamDateįROM. ,S1.Course AS S1_Course, S2.Course as S2_Course as S1_StudentId,S2.StudentId as S2_StudentID Self-Referencing method to finding duplicate students having same name, course, marks, exam date Let us look at the self-referencing method to find duplicates which looks like this: USE UniversityV2 In the self-referencing method, we take two references to the same table and join them using column-by-column mapping with the exception of the ID which is made less than or greater than the other. ![]() What if there are thousands of records in this table, then viewing the table won’t be much help. ![]() View the Student table to see duplicate records: - View Student table dataįinding Duplicates by Self-referencing Method Let us deliberately insert a duplicate record for Student: Asif to the Student table as follows: - Adding Student Asif duplicate record to the Student table So, we assume that no two students can have the same name, course, marks and exam date. ![]() In this case, a table is said to have duplicate records if a student’s Name, Course, Marks, and ExamDate coincide in more than one records even if the Student’s ID is different. Now we are going to introduce duplicate row(s) in the Student table. We are going to discuss now some potential scenarios in which duplicates were introduced and deleted starting from simple to slightly complex situations. You have successfully prepared the sample data by setting up a database with one table and two distinct (different) records. View the table which contains two distinct records at the moment: - View Student table data Let us only add two records to the Student table: - Adding two records to the Student table INT IDENTITY (1, 1) NOT NULL,ĬONSTRAINT PRIMARY KEY CLUSTERED ( ASC) (1) Create UniversityV2 sample database Start by creating a very simple database which consists of only a Student table at the beginning. Preparing Sample Dataīefore we start exploring the different options available to remove duplicates, it is worthwhile at this point to set up a sample database which will help us to understand the situations when duplicate data makes its way into the system and the approaches to be used to eradicate it. This article focuses on a specific scenario, when data inserted into a database table, leads to the introduction of duplicate records and then we will take a closer look at methods for removing duplicates and finally remove the duplicates using these methods. The presence of duplicate rows is a common issue that SQL developers and testers face from time to time, however, these duplicate rows do fall into a number of different categories that we are going to discuss in this article. This article discusses two different approaches available to remove duplicate rows from SQL table(s) which often becomes difficult over time as data grows if this is not done on time.
0 Comments
Read More
Leave a Reply. |