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.