Delete duplicate rows in sql – Remove duplicate rows Best 3 Methods

In delete duplicate rows in sql, Different ways to SQL delete duplicate rows from a SQL Table Examples. Like as SQL delete duplicate Rows using Group By and having clause, SQL delete duplicate Rows using Common Table, RANK function to SQL delete duplicate rows or Use SSIS package to SQL delete duplicate rows.

SQL query to delete duplicate rows

create a table like this,

create table Product(productid int,name varchar(20))

Method 1 : Delete All Duplicate Rows Or Records From SQL Table

select distinct * into ‪#‎tmptbl‬ From Product    
delete from Product    
insert into Product    
select * from #tmptbl drop table #tmptbl 

Method 2 : delete duplicate rows in sql

WITH cte AS (    
   SELECT productid , name ,    
   row_number() OVER(PARTITION BY productid , name order by productid ) AS [rn]    
   FROM dbo.Product    
)    
DELETE cte WHERE [rn] > 1 

Method 3 : Delete Duplicate Rows In SQL Server From A Table

delete from Product where productid in(select productid from Product group by productid having  count(*) >1)

Delete Duplicates From a Table in SQL Server

here We will learn step by step how to delete duplicate rows from a table in SQL Server Example.

Setting up a sample table

Step First of all, create a new table named members.informations as follows:

DROP TABLE IF EXISTS members.informations;

CREATE TABLE members.informations(
    information_id INT IDENTITY(1,1) PRIMARY KEY,
    profile_nm NVARCHAR(100) NOT NULL,
    account_details NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) NOT NULL,
);

Step Second, insert some rows into the members.informations table:

INSERT INTO members.informations
    (profile_nm,account_details,email) 
VALUES
    ('Krunal','Owner','[email protected]'),
    ('Chirag','Owner','[email protected]'),
    ('Parag','Devloper','[email protected]'),
    ('Parag','Devloper','[email protected]'),
    ('Parag','Devloper','[email protected]'),
    ('Sheetal','Manager','[email protected]'),
    ('Sheetal','Manager','[email protected]'),
    ('Hardik','Tester','[email protected]'),
    ('Hardik','Tester','[email protected]'),
    ('Kavita','Reception','[email protected]');

Step Third, query data from the members.informations table:

SELECT 
   information_id, 
   profile_nm, 
   account_details, 
   email
FROM 
   members.informations;

Also Read : sql query to find duplicate rows in a database

Delete duplicate rows from a table example

WITH cte AS (
    SELECT 
        information_id, 
        profile_nm, 
        account_details, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                profile_nm, 
                account_details, 
                email
            ORDER BY 
                profile_nm, 
                account_details, 
                email
        ) row_num
     FROM 
        members.informations
)
DELETE FROM cte
WHERE row_num > 1;

Remove duplicate rows

DELETE FROM members
LEFT OUTER JOIN (
   SELECT MIN(Member_id) as Member_id, fname, dp_id, status 
   FROM members 
   GROUP BY fname, dp_id, status
) as KeepRows ON
   members.Member_id = KeepRows.Member_id
WHERE
   KeepRows.Member_id IS NULL

SQL query to delete duplicate rows

Create a table MemberDtl1 with some duplicate rows

    create table members(memberid int,name varchar(20))

    insert into members values(1,'Ram')
    insert into members values (1,'Ram')
    insert into members values (2,'Kajal')
    insert into members values(3,'Syam')
    insert into members values(3,'Syam')
    insert into members values (3,'Syam')       
    

Example: delete duplicate rows in sql

       select distinct * into #tmp From members
       delete from members
       insert into members                
       select * from #tmp drop table #tmp
	
       select * from members

I hope you get an idea about delete duplicate rows in sql.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.