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.