How to delete duplicate rows in SQL Server

0 votes

How to delete duplicate rows where no unique row id exists?

The give table is

col1  col2 col3 col4 col5 col6 col7
annie  1    1    1    1    1    1 
annie  1    1    1    1    1    1
shelly 3    3    3    3    3    3
shelly 3    3    3    3    3    3

The final result should look like:

annie  1    1    1    1    1    1
shelly 3    3    3    3    3    3

The queries I tried did not work

Feb 10, 2022 in Database by Neha
• 9,060 points
559 views

1 answer to this question.

0 votes

CTEs and ROW_NUMBER can be combined together which will allow us to see which rows are deleted (or updated), so you can just change the DELETE FROM CTE... to SELECT * FROM CTE:

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

For example:

COL1    COL2    COL3    COL4    COL5    COL6    COL7
annie    1        1       1       1       1       1
shelly   3        3       3       3       3       3

This example finds the duplicate values by a single column col1 because of the PARTITION BY col1. 

Add PARTITION BY if you want to add multiple columns

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
answered Feb 10, 2022 by Vaani
• 7,020 points

Related Questions In Database

0 votes
1 answer

How to delete multiple rows in SQL where id = (x to y)

If you need to delete based on ...READ MORE

answered Sep 17, 2022 in Database by narikkadan
• 63,420 points
4,714 views
0 votes
0 answers

How to update Identity Column in SQL Server?

With 200 records currently, I want to ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,010 points
1,051 views
0 votes
0 answers

How to ALTER multiple columns at once in SQL Server

ALTER the data types of several columns in ...READ MORE

Aug 12, 2022 in Database by Kithuzzz
• 38,010 points
2,153 views
0 votes
0 answers

How to Create a real one-to-one relationship in SQL Server?

I have two tables, Country and Capital, ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 38,010 points
293 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10, 2022 in Others by Soham
• 9,700 points
876 views
0 votes
1 answer

Finding duplicate rows in SQL Server

Use this : select o.orgName, oc.dupeCount, o.id from organizations ...READ MORE

answered Aug 14, 2022 in Data Science by narikkadan
• 63,420 points
311 views
0 votes
0 answers

How can I delete using INNER JOIN with SQL Server?

In SQL Server 2008, I want to ...READ MORE

Aug 23, 2022 in Database by Kithuzzz
• 38,010 points
503 views
0 votes
1 answer

How to duplicate IDs with Hive QL / Impala / Python

Based on your example, supposing id2 always ...READ MORE

answered Oct 1, 2018 in Python by Priyaj
• 58,090 points
705 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

You can use the PIVOT function to ...READ MORE

answered Feb 8, 2022 in Database by Vaani
• 7,020 points
1,260 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
568 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP