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 in Database by Neha
• 6,260 points
18 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 by Vaani
• 5,040 points

Related Questions In Database

0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,360 points
574 views
0 votes
1 answer

How to sort the data in sql ?

Hi Akash,  If you want to sort ...READ MORE

answered May 29, 2019 in Database by sampriti
• 1,120 points
279 views
0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
15,427 views
0 votes
1 answer

How to detect a SQL table's existence in Java?

Hello, Depending on the DB, you can do ...READ MORE

answered May 13, 2020 in Database by Niroj
• 82,680 points
385 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 in Others by Soham
• 8,730 points
62 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,060 points
252 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
281 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 5,040 points
12 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 in Database by Vaani
• 5,040 points
17 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 in Database by Vaani
• 5,040 points
35 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP