在UPDATE语句中使用HAVING子句 [英] Using a HAVING clause in an UPDATE statement

问题描述

此查询

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

选择我想将ISValid位设置为0的记录.

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)

这是在MS SQL SERVER 2008上

This is on MS SQL SERVER 2008

谢谢！

推荐答案

您可以像这样加入该子查询:

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

这篇关于在UPDATE语句中使用HAVING子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！