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
• 8,940 points
178 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
• 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 in Database by narikkadan
• 37,660 points
377 views
0 votes
0 answers

How to update Identity Column in SQL Server?

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

Aug 9 in Database by Kithuzzz
• 21,060 points
408 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 in Database by Kithuzzz
• 21,060 points
564 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 in Database by Kithuzzz
• 21,060 points
51 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
• 9,670 points
273 views
0 votes
0 answers

Finding duplicate rows in SQL Server

I have a SQL Server data set ...READ MORE

Aug 13 in Data Science by Kithuzzz
• 21,060 points
67 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 in Database by Kithuzzz
• 21,060 points
106 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,100 points
367 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
• 7,020 points
320 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
• 7,020 points
173 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