0

There are plenty of posts on SO where a solution is given to take out rows that are in one way or form duplicate to other rows, leaving only 1.

What I am looking for is how I can delete all rows from my temp-table that do not have a unique ID:

ID other_values ----------------------------- 1 foo bar 2 bar baz 2 null 2 something 3 else

I don't care about the other values; once the ID is not unique, I want all rows out, the result being:

ID other_values ----------------------------- 1 foo bar 3 else

How can I do this?

Improve this question
3

3 Answers 3

Reset to default
3

Try this:

--delete all rows from my temp-table that do not have a unique ID DELETE from MYTABLE WHERE ID IN (SELECT ID FROM MYTABLE GROUP BY ID HAVING COUNT(*) > 1)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

this would delete all duplicates and not leave 1. That is, if an ID has more than one, it'd remove the ID completely versus just removing all but one instance.
@scsimon That is exactly what OP want
1

I would use a DELETE command in conjunction with a subquery to detect duplicates

DELETE FROM mytable WHERE ID IN (SELECT ID FROM mytable GROUP BY ID HAVING COUNT(*) > 1)
Improve this answer

2 Comments

this would delete all duplicates and not leave 1. That is, if an ID has more than one, it'd remove the ID completely versus just removing all but one instance
This is exacltly what the OP wants! See his example result set. All the rows with ID=2 have been removed.
-1

Use Cte to delete rows. 

WITH cte AS ( SELECT id ,Other_values ,ROW_NUMBER() OVER ( PARTITION BY id ORDER BY id ) rownum FROM mytable ) DELETE FROM cte WHERE rownum > 1
Improve this answer

1 Comment

This will leave one of the duplicated rows. And that isnt what OP want.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.