4

I am looking for an SQL query to select all records not selected by another query on the same table. Specifically I want to select all records which have duplicates of a particular field('fieldA') and then delete all but one of those records.

So a select statement might be something like the following (which doesn't work!): 

select * from table where id not in(select * from table group by fieldA)

If a single query is not possible then what would be the most efficient solution?

questioner
  • 1,144
  • 4
  • 14
  • 22

3 Answers3

3

Specifically I want to select all records which have duplicates of a particular field('fieldA') and then delete all but one of those records.

In that case, join it:

delete x 
from myTable x
 join myTable z on x.field = z.field
where x.id > z.id
Community
  • 1
  • 1
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • 2
    +1 nice, no aggregates! You should add to answer that this keeps the `MIN(ID)` if you want `MAX(ID)` then you just reverse the direction to `x.id < z.id` – Conrad Frix Jun 01 '11 at 19:33
  • Sorry for the delay in replying. This answer is brilliant, exactly what I was looking for - thanks! – questioner Jun 05 '11 at 21:30
1

Something like:

SELECT id FROM table WHERE id IN (
    SELECT MIN(id) FROM table GROUP BY fieldA HAVING COUNT(*) > 1
)
Sören Kuklau
  • 19,454
  • 7
  • 52
  • 86
0

If you change your not IN sub SELECT statement to only return one column (ID), it should work -

http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/

cchamberlain
  • 17,444
  • 7
  • 59
  • 72