更新符合特定条件的记录的过程 [英] Procedure for updating records meeting specific conditions

问题描述

大家好

用Sql存储过程来更新Sql server中的特定列。我需要在一周内的任何五天内定期购买的所有客户将CurCustType更新为R。



我尝试了什么：



查询： -

创建程序ModifyCustType 
 As 
 BEGIN 
 while（Count（CustName）> = 1）
 
更新[CustmrSrc]设置CurCstType ='R'
 
其中存在
 
（SELECT * FROM [CustmrSrc] a 
 JOIN（SELECT CustName FROM [CustmrSrc] 
 GROUP BY CUSTName，DATEPART（ISO_WEEK，PurchaseDate）
 HAVING COUNT（PurchaseDate）> = 5 ）b 
 ON a.CustName = b.CustName）
 
 END 

结束了错误：无效的列名'CustName'

我可以从中推断出我尝试的while条件有问题，但我在这里应用的具体条件是空白的。需要帮助。



问候

解决方案

：叹息：

哟你应该考虑给你的解决方案，而不是盲目地使用它们......



尝试：

 更新 a  SET  CurCstType = '  R'  FROM  [CustmrSrc] a 
  JOIN （ SELECT  CustName  FROM  [CustmrSrc] 
  GROUP   BY  CUSTName，DATEPART（ISO_WEEK，PurchaseDate）
  HAVING  COUNT（PurchaseDate）> =  5 ）b 
  ON  a.CustName = b .CustName 

Hi All
Stuck up with a Sql Stored Procedure to update a specific column in Sql server. I need to update the "CurCustType" to "R" for all those Customer who purchases regularly across any five days in a week.

What I have tried:

The query :-

Create Procedure ModifyCustType
As 
BEGIN
while (Count(CustName)>=1)

Update [CustmrSrc] set CurCstType = 'R'

where  Exists

(SELECT * FROM [CustmrSrc]  a
JOIN (SELECT CustName FROM [CustmrSrc]
GROUP BY CUSTName, DATEPART(ISO_WEEK,PurchaseDate)
HAVING COUNT(PurchaseDate) >= 5) b
ON a.CustName = b.CustName)

END

Its ending up with error: "invalid column name 'CustName'"
What i could infer from this is there is something wrong with the while condition i tried, but i was blank on the specific condition to be applied here.Need help on this.

Regards

解决方案

:sigh:
You are supposed to think about solutions you are given, not try to use them blindly...

Try:

UPDATE a SET CurCstType = 'R' FROM [CustmrSrc] a
JOIN (SELECT CustName FROM [CustmrSrc]
GROUP BY CUSTName, DATEPART(ISO_WEEK,PurchaseDate)
HAVING COUNT(PurchaseDate) >= 5) b
ON a.CustName = b.CustName

这篇关于更新符合特定条件的记录的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！