user18160 user18160 - 9 months ago 51
SQL Question

DELETE duplicate values in SQL

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

How should I delete the duplicate values ?


Answer Source

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 UserItemId and 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.