SQL Remove non duplicate entires in a table

Question

I have a table with two columns CountryCode CountryName. There are duplicate entries in countrycode. But I want to remove the non-duplicate entires and keep the rows which are duplicates in the countrycode column. So I am trying to write an SQL statement to do this. I think I have to use Having but not too sure how exactly to incorporate it. Thanks

Answer 1

That's a bit odd. I was expecting you to want to remove the duplicate entries, not the other way around. But something like this should work regardless of the database you are using:

delete from TableName
 where CountryCode in (select CountryCode
                         from TableName
                        group by CountryCode
                        having count(*) = 1).

So to be clear, the subquery:

select CountryCode
  from TableName
 group by CountryCode
having count(*) = 1

... returns rows with unique CountryCodes. And then the delete statement:

delete from TableName
 where CountryCode in (...)

... deletes those unique rows so that the only rows remaining in your table should be the ones with duplicates.

However, by your comments, it sounds like you just want a query that returns only the duplicates. If that's the case, then just use the subquery inside a select statement, but modify the having clause to return only duplicates:

select *
  from TableName
 where CountryCode in (select CountryCode
                        from TableName
                       group by CountryCode
                      having count(*) > 1)