This query
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
ORDER BY 'Count' DESC
Selects records that I would like to set an ISValid
bit to 0
.
These records are records that appear twice in my database due to an input error.
I'm looking for something like:
UPDATE NCAAstats
SET IsValid = 0
WHERE (my select statement)
This is on MS SQL SERVER 2008
Thanks!
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