Issue deleting duplicates using CTE with more than one base table

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

Open in new window

crashjamAsked:
Who is Participating?
 
crashjamConnect With a Mentor Author Commented:
I placed a new column 'awardid' on table for distinction and ran the script below to achieve what was needed.

SET NOCOUNT ON
SET ROWCOUNT 1

WHILE 1 = 1
      BEGIN
            DELETE FROM tbl_AwardsEnroll
            WHERE AwardId IN
                        (
                        SELECT MAX(AwardId) FROM tbl_AwardsEnroll  AWARDS
                              join tbl_MyCourse PLANS on PLANS.CourseID = AWARDS.CourseID
                              where AwardDate is not null and FullCertAchieved = 'Yes'                               
                              group by CandidateID, CourseTitle, CourseLevel
                              having count(*) > 1                              
                        )
            IF @@ROWCOUNT = 0
                  BREAK;
      END
SET ROWCOUNT 0
0
 
keyuConnect With a Mentor Commented:
Microsoft SQL Server Limitations on Updating Database Views
Microsoft SQL Server does not permit updating fields in multiple database tables used within a SQL view.  According to Microsoft, “INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable… UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view.  A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause."

The modifications made by the UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view.  In general, you should create an INSTEAD OF UPDATE trigger to update only the field that you want in your view.”

Microsoft also offers this information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_1mpf.asp


REf Link:http://www.ironspeed.com/Designer/3.2.4/WebHelp/Part_VI/View_or_function_XXX_is_not_updatable_because_the.htm
0
 
Scott PletcherSenior DBACommented:
I guess you're trying to DELETE from the Awards table??


Something like this should do it:


DELETE FROM AWARDS2
FROM dbo.tbl_AwardsEnroll AWARDS2
INNER JOIN (
    select AWARDS.CandidateID, AWARDS.CourseID,
    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'
) AS FindDups ON
    FindDups.RowNo = 1 AND
    FindDups.CandidateID = AWARDS2.CandidateID AND
    FindDups.CourseID = AWARDS2.CourseID
0
 
crashjamAuthor Commented:
select CandidateID, CourseTitle, Courselevel, COUNT(*) FROM tbl_AwardsEnroll AWARDS
join tbl_MyCourse PLANS on PLANS.CourseID = AWARDS.CourseID
where AwardDate is not null and FullCertAchieved = 'Yes' 
group by CandidateID, CourseTitle, Courselevel
having count(*) > 1

Open in new window


In fact let me start from base. I used the code above to find my duplicates. I want to delete and leave only one instance of Candidate, CourseTitle, Courselevel.

So in my original attempt with the CTE I included ordered by AwardDate so it would leave the first instance only and all other duplicates deleted.
Thanks keyu. I realize this and now looking for the proper coding to implement. I will play some more with ScottPletcher's coding as the resultset brought back for Del is too much so need to do some tweaking .
0
 
crashjamAuthor Commented:
It's implementation achieved the desired outcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.