Using a HAVING clause in an UPDATE statement

Question

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!

score 26 · Accepted Answer · answered Jan 09 '12 at 19:25

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

score 4 · Answer 2 · answered Jan 09 '12 at 19:26

4

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

answered Jan 09 '12 at 19:26

Mithrandir

24,869
6
50
66

score 3 · Answer 3 · answered Jan 09 '12 at 19:32

3

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.

answered Jan 09 '12 at 19:32

Segev -CJ- Shmueli

1,535
14
15

For what it's worth, that will go really slowly, since the subquery will be executed against each row. – Eric Jan 09 '12 at 19:35
2

@Eric - I don't think it will. What are you basing that on? – JNK Jan 09 '12 at 19:38
@JNK - You're correct--the `in` causes a semi join in SQL 2008. My fault--forgot the engine. Sorry for the confusion :) – Eric Jan 09 '12 at 19:39
@Eric - no problem, just wanted to make sure you didn't know something I had missed :) – JNK Jan 09 '12 at 19:41

jmoreno · Answer 4 · 2013-11-12T16:41:13.123

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.

score -1 · Answer 5 · edited May 31 '19 at 19:34

-1

For SQL Server 17

UPDATE table SET col = val 
(SELECT cols FROM table .. )

edited May 31 '19 at 19:34

hotfix

3,376
20
36

answered May 31 '19 at 17:15

Quickee

321
1
6
13

Using a HAVING clause in an UPDATE statement

5 Answers5

Linked