How to Delete Duplicate Rows in SQL 2000?
- by Mikecancook
I thought I had this figured out but it turns out I'm just deleting the first record. The following returns the duplicate rows. All have a count of 2. I just want to delete the first one for each duplicate record.
select scorestudentid, scoreadvisor, scorecorrect, count(*)
from scores
where scoretestid = 3284
group by scorestudentid, scoreadvisor, scorecorrect
having count(scorestudentid) > 1
Which returns:
scorestudentid scoreadvisor scorecorrect no column name
13033719 28059 3.0 2
13033777 28086 3.0 2
13033826 28147 3.0 2
13033960 28023 3.0 2
So I put this together thinking it would work:
set rowcount 1
delete
from scores
where scoretestid = 3284
and scorestudentid in (
select scorestudentid
from scores
where scoretestid = 3284
group by scorestudentid
having count(scorestudentid) > 1)
It really seems like it should be a simple concept but I'm not getting it.
