I've used CTE to remove duplicates when i don't have primary keys however never tried it where I needed to join with another table to identity my duplicate records.
Now I'm getting the error
View or function 'DelDup' is not updatable because the modification affects multiple base tables.
Note the code below. Trying to determine best approach to rewrite.
;with DelDup as (select row_number() over (partition by
AWARDS.CandidateID, PLANS.CourseTitle, PLANS.Courselevel order by CandidateID, CourseTitle, Courselevel, AwardDate) as RowNo
from tbl_AwardsEnroll AWARDS join tbl_MyCourse PLANS on PLANS.CourseID = AWARDS.CourseID
where AWARDS.AwardDate is not null and AWARDS.FullCertAchieved = 'Yes'
Delete from DelDup where RowNo> 1