aspx Tutorial

NET Articles,jQuery demo, asp.net with jQuery, online tutorial,Jquery, SilverLight, Javascript, asp.net,JSON, MVC,.NET Articles,demo, Web Services,
Advertise Here

Toolbar

Get our toolbar!

Advertize



Posted by Aamir Hasan   on Sunday, June 27, 2010 Total Views:  

In this Article, i will tell you some technique that how can we delete a duplicate rows from a table. Why this duplicate values exist in table it mean that a problem is in the code logic and table desgin. Read database normalization to correct the structure of your database. Let's Design the table structure called  Patient Table. And insert some data.

Select duplicate rows from a Patient Table with  primary key in a table

Check if Patient table exists in the database then drop it and create it.

 

IF OBJECT_ID('PATIENT') IS NOT NULL
    DROP TABLE PATIENT
CREATE TABLE PATIENT
    (
      ID BIGINT IDENTITY NOT NULL PRIMARY KEY,
      FULLNAME NVARCHAR(MAX) ,
      CCID BIGINT ,
      AGE INT ,
      GENDER CHAR(10) ,
      BIRTHDAY SMALLDATETIME ,
      REGISTATIONDATETIME DATETIME ,
      ISDELETED BIT DEFAULT ( 0 )
    )

 

 

INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 101, 23, 'MALE', '1990-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AMIR ALI', 102, 23, 'MALE', '1993-02-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AHMED ALI', 103, 23, 'FEMALE', '1994-08-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'SONIA KHAN', 104, 23, 'FEMALE', '1991-07-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AWAIS AHMED', 105, 23, 'MALE', '1992-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR KHAN', 106, 23, 'MALE', '1997-01-05', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'SOBIA HINA', 107, 23, 'FEMALE', '1988-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'ADNAN KHAN', 106, 23, 'MALE', '1987-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 108, 23, 'MALE', '1997-04-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 101, 23, 'MALE', '1990-01-01', GETDATE(), 0 )

 

 

Now you have notice that some duplicate values are loaded in Patient table. Select Duplicate records from a Patient Table

SELECT  *
        FROM patient
WHERE   ID NOT IN ( SELECT  MAX(ID)
                    FROM    patient
                    GROUP BY fullname )

Delete Duplicate Rows From a Patient Table as you see in below Query

DELETE
        FROM patient
WHERE   ID NOT IN ( SELECT  MAX(ID)
                    FROM    patient
                    GROUP BY fullname )

Above Query has deleted those Patient records who have duplicate CCID Number. Now, Select all Patient Table records to verify that all duplicate records deleted.

SELECT  *
FROM    patient

Another way to Select and delete Duplicate rows from a table

Select patient id which have duplicate rows

 

SELECT  *
FROM    ( SELECT    id ,
                    row_number() OVER ( PARTITION BY ccid ORDER BY ccid ) AS dupid
          FROM      patient
        ) temp1
WHERE   temp1.dupid >= 2

Delete rows which have duplicate rows

DELETE  FROM patient
WHERE   id IN (
        SELECT  id
        FROM    ( ( SELECT  id ,
                            row_number() OVER ( PARTITION BY ccid ORDER BY ccid ) AS dupid
                    FROM    patient
                  )
                )temp1
        WHERE   temp1.dupid >= 2 )

 

 

Another way to Select and delete Duplicate rows from a table using Temporary Table

 

SELECT  id ,
                row_number() OVER ( PARTITION BY ccid ORDER BY ccid ) AS dupid
        INTO    #temptable
        FROM    patient
       
        SELECT  *
        FROM    patient
        WHERE   id IN ( SELECT  id
                        FROM    #temptable )

 

 

Select duplicate rows from  a table using common table expression

 

WITH    duplicateCCID
          AS ( SELECT   id ,
                        row_number() OVER ( PARTITION BY ccid ORDER BY ccid ) AS dupid
               FROM     patient
             )
    SELECT  *
    FROM    duplicateCCID
    WHERE   dupid >= 2 

delete duplicate rows from a table using common table expression (CTE)

WITH    duplicateCCID
          AS ( SELECT   id ,
                        row_number() OVER ( PARTITION BY ccid ORDER BY ccid ) AS dupid
               FROM     patient
             )
    DELETE
    FROM    duplicateCCID
    WHERE   dupid >= 2

 

 

Select duplicate rows from a Patient Table with no primary key in a table

Below query define Common Table Expression (CTE). i have used DENSE_RANK() function to group the records based on CCID Column

IF OBJECT_ID('PATIENT') IS NOT NULL
    DROP TABLE PATIENT
CREATE TABLE PATIENT
    (
    
      FULLNAME NVARCHAR(MAX) ,
      CCID BIGINT ,
      AGE INT ,
      GENDER CHAR(10) ,
      BIRTHDAY SMALLDATETIME ,
      REGISTATIONDATETIME DATETIME ,
      ISDELETED BIT DEFAULT ( 0 )
    )

INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 101, 23, 'MALE', '1990-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AMIR ALI', 102, 23, 'MALE', '1993-02-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AHMED ALI', 103, 23, 'FEMALE', '1994-08-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'SONIA KHAN', 104, 23, 'FEMALE', '1991-07-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AWAIS AHMED', 105, 23, 'MALE', '1992-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR KHAN', 106, 23, 'MALE', '1997-01-05', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'SOBIA HINA', 107, 23, 'FEMALE', '1988-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'ADNAN KHAN', 106, 23, 'MALE', '1987-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 108, 23, 'MALE', '1997-04-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR HASAN', 101, 23, 'MALE', '1990-01-01', GETDATE(), 0 )
INSERT  INTO PATIENT
VALUES  ( 'AAMIR KHAN', 107, 23, 'MALE', '1990-01-01', GETDATE(), 0 )

 

WITH    PatientCTE
          AS ( SELECT   * ,
                        RANKING = DENSE_RANK() OVER ( PARTITION BY CCID ORDER BY NEWID() ASC )
               FROM     PATIENT
             )
    SELECT  *
    FROM    PatientCTE
    WHERE   RANKING >= 2

 


 

Another Way

 

WITH    PatientCTP
          AS ( SELECT   DupID = ROW_NUMBER() OVER ( PARTITION BY ccID ORDER BY ( SELECT
                                                              1
                                                              ) ) ,
                        *
               FROM     patient
             )
    SELECT  *
    FROM    PatientCTP
    WHERE   dupid >= 2
    ORDER BY ccid

 

Delete Duplicate Rows from a Patient Table with no Primary Key

 

WITH    PatientCTE
          AS ( SELECT   * ,
                        RANKING = DENSE_RANK() OVER ( PARTITION BY CCID ORDER BY NEWID() ASC )
               FROM     PATIENT
             )
    DELETE  FROM PatientCTE
    WHERE   RANKING >= 2

 

 

I have used Common Table Expression to Select and Delete Duplicate records from a Paitent Table. Common Table Expression is Virtual Table. I have deleted records from the PatientCTE that have Ranking greater then and eqwal to 2. Above Query will remove  all duplicate records.

Now Select the all Patient records to verify it that duplicate rows a re deleted.

 

Download

Delete Duplicate rows With Primary Key.sql (1.79 kb)

Delete Duplicate rows With Primary Key.sql (1.79 kb)

Tags:
Category: All
Protected by Copyscape Online Plagiarism Tool

Comments (1) -

Clair Stoffregen
Clair Stoffregen United States
7/13/2015 8:21:24 AM #

We stumbled over here by a different website and thought I may as well check things out. I like what I see so i am just following you. Look forward to looking at your web page repeatedly.|

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Advertizement 1
Advertizement 2
Advertizement 3
Advertizement 4
Advertizement 5