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)