SQL Tips #31 - Deleting Duplicate Entries While Keeping the Original

Duplicate rows happen - this query deletes duplicate entries from a table while keeping the original (proto) entry.

The Query

delete FROM tblBookingComment WHERE commentid in (
SELECT commentid FROM (
    SELECT 
        (SELECT top 1 commentid FROM tblBookingComment 
        WHERE addedby=1 and bookingid=comments.bookingid and comment=comments.comment) as protocomment,* 
        FROM tblBookingComment as comments WHERE addedby=1 and 
        (select count(*) from tblBookingComment where addedby=1 
        AND bookingid=comments.bookingid and comment=comments.comment
        group by bookingid, comment) > 1
    and commentid <> (
                            SELECT top 1 commentid 
                            FROM tblBookingComment 
                            WHERE bookingid=comments.bookingid and comment=comments.comment
                           )
    ) as t
)

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags