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)