Using a HAVING clause in an UPDATE statement
You can join to that subquery like so:
update n1 set
isvalid = 0
from
ncaastats n1
inner join (
SELECT
FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, StatTypeId, COUNT(*) AS 'Count'
FROM NCAAstats
INNER JOIN College_Translator
ON College_Translator.AccountID = NCAAstats.AccountId
GROUP BY FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, CalendarYear, StatTypeId
HAVING COUNT(*) >1
) n2 on
n1.accountid = n2.accountid
Use a CTE, and do what is basically a self join
;with NCAAstatsToUpdate(
SELECT AccountId
FROM NCAAstats n
INNER JOIN College_Translator ct
ON ct.AccountID = n.AccountId
GROUP BY FirstName, LastName, n.AccountId, ct.school_name,
CalendarYear, StatTypeId
HAVING COUNT(*) >1 )
UPDATE NCAAstats
SET IsValid=0
FROM NCAAstats n
inner join NCAAstatsToUpdate u
on n.AccountId = u.AccountId
Or better yet, use the windowing functions.
;with NCStats as(
Select distinct row_number() over (partition by FirstName, LastName, n.AccountId, ct.school_name,
CalendarYear, StatTypeId order by n.accountId) rw, n.*
FROM NCAAstats n
INNER JOIN College_Translator ct
ON ct.AccountID = n.AccountId
)
Update NCStats
Set IsValid=0
Where rw>1
Note that second does not update the "first" record to invalid, and that it assumes that there that there is a 1 to 1 relationship between NCAAstats and College_Translator.
SQL Server can do updates like:
UPDATE table SET col=vaue
FROM (
SELECT ......
)
You should look here first:
http://msdn.microsoft.com/en-us/library/aa260662(v=sql.80).aspx
The above are good suggestions.... here's another easy way to do it :
update ncaastats set isvalid = 0
where accountId in (
SELECT AccountId
FROM NCAAstats
INNER JOIN College_Translator
ON College_Translator.AccountID = NCAAstats.AccountId
GROUP BY FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, CalendarYear, StatTypeId
HAVING COUNT(*) >1
)
** Forgive me if I messed up the columns name, but you get the idea.