Click here to Skip to main content
15,890,845 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 22-Aug-16 7:41am

1 solution

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

Try:
SQL
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
 
Share this answer
 
Comments
Maciej Los 22-Aug-16 17:03pm    
5ed!
mousau 23-Aug-16 2:24am    
Hi Griff
Thanks a lot for the solution.I have to learn a lot;Tactical to Practical:)I am a newbie, Thanks a lot for the help:)
Thank You
Regards
mousau 23-Aug-16 2:33am    
Hi Gotta a question!! where to find those emoticons in this forum !! I was searching:(
Regards

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