Click here to Skip to main content
15,897,519 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have been trying to delete duplicate rows from one table using cte .but i am getting error from that.please help me since it is urgent.thank u.query and result is ther in down..

with cte(cust,fs,rt)
as
(
select cd.c_cust_code cust,cd.c_fs_code fs
,ROW_NUMBER() over(partition by cd.c_cust_code order by cd.c_fs_code ) rt from Tbl_Cust_Div cd
join
(
select c_cust_code,c_fs_code,d_date_to
--,c_div_code
from Tbl_Cust_Div where d_date_to='2016/09/24'
group by c_cust_code,c_fs_code,d_date_to--,c_div_code
having COUNT(*)>1
)h on h.c_cust_code=cd.c_cust_code and h.c_fs_code=cd.c_fs_code and h.d_date_to=cd.d_date_to
--and h.c_div_code=cd.c_div_code
)

delete from cte where rt<>1


error is-

Msg 4405, Level 16, State 1, Line 1
View or function 'cte' is not updatable because the modification affects multiple base tables.
Posted
Comments
Sergey Alexandrovich Kryukov 27-Jan-16 0:39am    
Why not preventing duplicates in first place, when you add data?
—SA
Member 11337367 27-Jan-16 0:40am    
That is past now what is the solution for that..
jgakenhe 27-Jan-16 0:47am    
That is a tough one. The guys at StackOverFlow took out the JOIN and used an EXIST; which helped that user: http://stackoverflow.com/questions/22761972/delete-from-cte-with-join

1 solution

You can find a good sample doing almost same as you are doing over here![^]

This can be a good tutorial as well.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900