Search code examples
sqlsql-server-2012

Delete ALL rows that have a duplicate ID

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?

Solution

  • 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)