Registrations AS R1
INNER JOIN Registrations AS R2
ON R2.UserItemId = R1.UserItemId
AND R2.CourseOfferingId = R1.CourseOfferingId
AND R2.Id != R1.Id
R2.Id > R1.Id
I'll take a stab at your problem. This is only a guess, based on the query you provided. A complete question would have described what exactly you mean by a duplicate row.
delete from Registrations where exists ( select 1 from Registrations r2 where r2.UserItemId = Registrations.UserItemId and r2.CourseOfferingId = Registrations.CourseOfferingId and r2.Id < Registrations.Id );
Apparently you have multiple rows that have the same
CourseOffereningId but different
Id values. I'm presuming that you want to keep the one with the lowest
Id and discard the others.
The subquery in the query takes each row in the table and checks to see if there's another row like it but with a lower Id. If the answer if yes then the row is deleted.