How to delete only few rows if several rows are duplicated in SQL?

Question

How to delete only few rows (more than 1) if several rows are duplicated? When I try to put condition all rows are getting deleted. I'm using SQL server 2008.

Possible duplicate of [How to delete duplicate rows in sql server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Blorgbeard, Jul 05 '17 at 16:36
Hello & Welcome. Please have a look at [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) - We're here to help you with specific programming problems, not to write code for you. Please show us what you've tried so far, share your thoughts with us and we'll help you find a solution, but nobody is going to write code for you. According to [What topics can I ask about here?](https://stackoverflow.com/help/on-topic) your question is actually Off-Topic to Stackoverflow. I ask you to edit your question according to our guidelines, otherwise you won't get that much help — Yagami Light, Jul 05 '17 at 16:38

score 1 · Accepted Answer · answered Aug 13 '17 at 14:07

1

Delete using top command.

DELETE TOP(n) TableName
WHERE (Condition which pulls duplicate rows)
where n is the number of rows to delete.

answered Aug 13 '17 at 14:07

Bharath Kumar

48
1
8

score 0 · Answer 2 · edited Jul 05 '17 at 16:42

0

Below SQL would help you.

DELETE TOP(n) TableName
WHERE (Condition which pulls duplicate rows)

where n is the number of rows to delete.

Tip: Always try to use Primary key (A primary key is used to uniquely identify each row in a table) as it can have significant impact on the performance, usability, and the extensibility of the entire database.

edited Jul 05 '17 at 16:42

KenD

5,280
7
48
85

answered Jul 05 '17 at 16:40

madhukar mohan

37
7

Rakesh Soni · Answer 3 · 2017-07-05T17:04:53.577

Use below query

-- Check : This query will give you unique records 
SELECT MAX(ID)
FROM MyTable
GROUP BY Column1, Column2, Column3 ......

-- Now delete the record apart from unique record
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY Column1, Column2, Column3 ......)

Note : ID is primary key here. if ID does not exist in your table then first create the ID with AUTO_INCREMENT value then run the above query.

Example :

MyTable

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
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

if ID does not exist add ID with auto increment value. After adding ID

ID  col1  col2 col3 col4 col5 col6 col7
1   john  1    1    1    1    1    1 
2   john  1    1    1    1    1    1
3   john  1    1    1    1    1    1
4   sally 2    2    2    2    2    2
5   sally 2    2    2    2    2    2

-- Check : This query will give you unique records

SELECT MAX(ID) FROM MyTable
GROUP BY col1, col2, col3, col4, col5, col6, col7

Query output Max(ID)
3
5

-- Now delete the record apart from unique record

DELETE
FROM MyTable
    WHERE ID NOT IN
    (
     SELECT MAX(ID) FROM MyTable
     GROUP BY col1, col2, col3, col4, col5, col6, col7)

score 0 · Answer 4 · answered Jul 05 '17 at 17:42

This answer assumes a primary key in the table.

After writing this one out, I looked at the answer linked as a possible duplicate (How to delete duplicate rows in sql server?). I like the CTE method much better.

CREATE TEST DATA

/* Create test data tables */
IF OBJECT_ID('tempdb.dbo.#td', 'U') IS NOT NULL
  DROP TABLE #td; 

CREATE TABLE #td ( id int identity, data int NOT NULL ) ;

INSERT INTO #td (data)
VALUES (1),(1),(2),(3),(3),(3),(1),(3)
;

MY ORIGINAL

/* Test data includes dupes. */
SELECT * FROM #td ;

/* Now find and DELETE the dupes. */
DELETE FROM #td 
WHERE id IN (
    SELECT sq.id
    FROM (
        SELECT id, ROW_NUMBER() OVER ( PARTITION BY data ORDER BY (data) ) AS rn
        FROM #td
    ) sq
    WHERE sq.rn > 1
)
;

/* Dupes are gone. */
SELECT * FROM #td ;

BETTER

SELECT * FROM #td ; 

WITH CTE AS(
   SELECT data, RN = ROW_NUMBER()OVER(PARTITION BY data ORDER BY data)
   FROM #td
)
DELETE FROM CTE WHERE RN > 1

SELECT * FROM #td ;

score 0 · Answer 5 · answered Aug 14 '17 at 12:11

0

Use delete top command And give the number of rows inside parenthesis. Delete top() from table

answered Aug 14 '17 at 12:11

Deepak M

51
1
5

score 0 · Answer 6 · answered Aug 15 '17 at 11:24

CREATE TABLE TabStack ( col1 varchar(10), col2 varchar(10), col3 date )

insert into TabStack values(

'john','conner','01.01.1980'), ('sarah','conner','03.04.1978'), ('john','conner','01.01.1977')

with cte as ( select col1, col2, col3, ROW_NUMBER() over(partition by col1,col2 order by col3) rn from TabStack )

delete from cte where rn>1

How to delete only few rows if several rows are duplicated in SQL?

6 Answers6