如何编写cte从重复表中删除数据？ [英] how to write cte to delete data from duplicate table?

问题描述

我一直在尝试使用cte从一个表中删除重复的行。但是我从那里得到错误。请帮助我，因为它很紧急。谢谢你。结果和结果是下来..



与cte（cust，fs，rt）

as

（

select cd.c_cust_code cust，cd.c_fs_code fs

，ROW_NUMBER（）over（cd.c_cust_code order by cd.c_fs_code）rt from Tbl_Cust_Div cd

join

（

选择c_cust_code，c_fs_code，d_date_to

- ，c_div_code
来自Tbl_Cust_Div的
，其中d_date_to ='2016/09/24'

group by c_cust_code，c_fs_code，d_date_to - ，c_div_code

有COUNT（*）> 1

）h on h.c_cust_code = cd .c_cust_code和h.c_fs_code = cd.c_fs_code和h.d_date_to = cd.d_date_to

- 和h.c_div_code = cd.c_div_code

）



从cte中删除rt<> 1





错误是 -



消息4405，等级16，状态1，行1

视图或函数'cte'不可更新，因为修改会影响多个基表。

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.

推荐答案

你可以找到一个好的样本做几乎和你在这里做的一样！ [ ^ ]



这也是一个很好的教程。

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

This can be a good tutorial as well.

这篇关于如何编写cte从重复表中删除数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！