优化sql语句

Question

	update dbo.HC_HouseFollow set Contents='VIP'+Contents
				where  FollowID in (select FollowID from (select FollowID,Contents from (select FollowID,Contents from  dbo.HC_HouseFollow  
				inner join dbo.HC_HousingResourcesBase
				on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID
				where HC_HouseFollow.IsDelete=0 and IsVIP=1  and CHARINDEX('VIP',Contents)=0   and PATINDEX('%[0-9]%', Contents)<>1) as a
				where CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('新增房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('认领房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('跨区转介房源未发生转介！系统自动转入认领区！房源编号',Contents)<>0)as b)

				update dbo.HC_HouseFollow set Contents=left(Contents,10)+'VIP'+right(Contents,len(Contents)-10)
				where  FollowID in (select FollowID from (select FollowID,Contents from (select FollowID,Contents from  dbo.HC_HouseFollow  
				inner join dbo.HC_HousingResourcesBase
				on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID
				where HC_HouseFollow.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',Contents)=0  and PATINDEX('%[0-9]%', Contents)=1 ) as a
				where CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('新增房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('认领房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
				or CHARINDEX('跨区转介房源未发生转介！系统自动转入认领区！房源编号',Contents)<>0)as b) 
优化sql语句

---

please verify my account

Answer 1

光看 CHARINDEX 就已经觉得没救了

Answer 2

怎么修改？

---

please verify my account

Answer 3

你好，lctk

如果你是想结合表“HC_HousingResourcesBase”并依据某些条件对表“dbo.HC_HouseFollow”做更新，你可以参考下如下的代码。

----方法1------
update HF
	SET HF.Contents = left(HF.Contents,10)+'VIP'+right(HF.Contents,len(HF.Contents)-10)
from dbo.HC_HouseFollow HF
inner join dbo.HC_HousingResourcesBase HRB
on HF.HousingResourcesID=HRB.HousingResourcesID
	where 
		HF.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',HF.Contents)=0  and PATINDEX('%[0-9]%', HF.Contents)=1 
		and
		(
			CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',HF.Contents)<>0
			or CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',HF.Contents)<>0
			or CHARINDEX('新增房源无店长点评！系统自动转入认领区！房源编号',HF.Contents)<>0
			or CHARINDEX('认领房源无店长点评！系统自动转入认领区！房源编号',HF.Contents)<>0
			or CHARINDEX('跨区转介房源未发生转介！系统自动转入认领区！房源编号',HF.Contents)<>0
		)
----方法2------
;with cte as
(
	select FollowID
	from dbo.HC_HouseFollow 
	inner join dbo.HC_HousingResourcesBase on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID
	where 
		HC_HouseFollow.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',Contents)=0  and PATINDEX('%[0-9]%', Contents)=1 
		and
		(
			CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
			or CHARINDEX('超十天未跟进！系统自动转入认领区！房源编号',Contents)<>0
			or CHARINDEX('新增房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
			or CHARINDEX('认领房源无店长点评！系统自动转入认领区！房源编号',Contents)<>0
			or CHARINDEX('跨区转介房源未发生转介！系统自动转入认领区！房源编号',Contents)<>0
		)
)

update dbo.HC_HouseFollow
set Contents=left(Contents,10)+'VIP'+right(Contents,len(Contents)-10)
where exists (select 1 from cte where HC_HouseFollow.FollowID = cte.FollowID)

Best Regards,

Albert Zhang