prads.cs (8/17/2012)
Sorry guys for the trouble ... just figured out some crazy method :hehe:UPDATEtvLRF
SETHasMultiple = 1
FROM@tv_LicenseRenewalFee tvLRF
INNER JOIN (SELECTGroupCode, MIN(SUBSTRING(ClassificationCode,3, LEN(ClassificationCode))) AS Number
FROM@tv_LicenseRenewalFee
WHEREGroupCode <> (
SELECTGroupCode
FROM@tv_LicenseRenewalFee
WHEREHasMultiple = 1
)
GROUP BY GroupCode
HAVING COUNT(GroupCode) > 1) A
ON(A.GroupCode+''+CAST(A.Number AS VARCHAR(2))) = tvLRF.ClassificationCode
Gosh, I'm sure it doesn't have to be quite so complicated. Try this - it generates exactly the same results:
;WITH Updater AS (
SELECT LicenseId, ClassificationCode, GroupCode, HasMultiple,
MaybeMultiple = ROW_NUMBER() OVER(PARTITION BY GroupCode ORDER BY ClassificationCode)
FROM @tv_LicenseRenewalFee
) UPDATE Updater SET HasMultiple = 1 WHERE MaybeMultiple = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden