Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Issue deleting duplicates using CTE with more than one base table

Posted on 2012-09-05
5
Medium Priority
?
421 Views
Last Modified: 2013-01-09
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

0
Comment
Question by:crashjam
  • 3
5 Comments
 
LVL 9

Assisted Solution

by:keyu
keyu earned 1500 total points
ID: 38371221
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38372676
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
 

Author Comment

by:crashjam
ID: 38372973
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
 

Accepted Solution

by:
crashjam earned 0 total points
ID: 38743337
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
 

Author Closing Comment

by:crashjam
ID: 38758203
It's implementation achieved the desired outcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question