How to delete duplicate rows in SQL Server

0 votes

How can I delete duplicate rows where no unique row id exists?

My table is

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
john  1    1    1    1    1    1
sally 2    2    2    2    2    2
sally 2    2    2    2    2    2

I want to be left with the following after the duplicate removal:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

I've tried a few queries but I think they depend on having a row id as I don't get the desired result. For example:

DELETE
FROM table
WHERE col1 IN (
    SELECT id
    FROM table
    GROUP BY id
    HAVING (COUNT(col1) > 1)
)
Jun 20, 2022 in Others by polo
• 1,480 points
487 views

1 answer to this question.

0 votes
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

without keeping original

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
answered Jun 21, 2022 by nisha
• 2,210 points

Related Questions In Others

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
896 views
0 votes
1 answer

Excel How to Remove Duplicate Rows in multiple of 3 Same Value

Put the following formula into a helper ...READ MORE

answered Dec 28, 2022 in Others by narikkadan
• 63,420 points
338 views
0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
1,686 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL server?

In SQL Server, it is possible to insert ...READ MORE

answered May 30, 2022 in Others by anisha
• 140 points
370 views
0 votes
1 answer

How can we UPDATE from a SELECT in an SQL Server

We can firstly use SELECT statement to fetch ...READ MORE

answered May 27, 2022 in Others by Avinash
• 240 points
4,009 views
0 votes
1 answer

How Can I use "Date" Datatype in sql server?

Your dates are interpreted as MM-DD-YYYY. This ...READ MORE

answered Jun 21, 2022 in Others by nisha
• 2,210 points
337 views
0 votes
1 answer
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, ...READ MORE

answered Jun 20, 2022 in Others by nisha
• 2,210 points
3,925 views
0 votes
1 answer

How to generate the "create table" sql statement for an existing table in postgreSQL

Create a table for a demo: CREATE TABLE ...READ MORE

answered Jun 20, 2022 in Others by nisha
• 2,210 points
326 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