66

I'm trying to execute this on MS-SQL but returns me an error just at the Group by line

update #temp
Set Dos=Count(1)
From Temp_Table2010 s
where Id=s.Total and s.total in (Select Id from #temp)
group by s.Total

Do anyone knows how can I solve this problem having good performance.

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Gerardo Abdo
  • 1,150
  • 3
  • 10
  • 16
  • Can you describe what you're trying to do here? I'll try to help you re-write the query, but I can't figure out what you're trying to accomplish. Since the table names aren't meaningful, maybe you can at least give a list of columns in each and a sample of the data you're looking at. – SqlRyan May 18 '10 at 05:16
  • So basically, you cannot use an UPDATE statement along with GROUP BY. – Divakar Nov 29 '22 at 09:13

3 Answers3

93

Try 

;with counts 
AS 
( 
    SELECT total, COUNT(*) as dos
    FROM temp_table2010 
    WHERE total in (select id from #temp)
)
UPDATE T 
SET dos=counts.dos
FROM #temp T 
INNER JOIN counts 
    ON t.id = counts.total
Code Magician
  • 23,217
  • 7
  • 60
  • 77
  • 4
    this is referred to as using a Common Table Expression (or CTE.) some docs - https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx and https://msdn.microsoft.com/en-us/library/ms175972.aspx – Don Cheadle Nov 09 '15 at 15:58
  • What's with the semicolon at the start of `;with counts`, does that serve a purpose other than closing the previous statement? – Stefan Collier Dec 12 '17 at 16:25
  • 7
    "with" means something else when it's not the beginning of a statement. Semicolons are optional in T-sql so the parser has to infer where semicolons should go. If there is a select statement immediately preceeding the CTE, it's ambigious to the parser if with is part of the previous statement or it's own. the leading semicolon makes that explicit. It's been adopted as a convention to prevent any parsing issues. – Code Magician Dec 13 '17 at 04:26
32

In SQL Server you can do aggregation in an update query you just have to do it in a subquery and then join it on the table you want to update.

UPDATE  #temp
SET     Dos = Cnt
FROM    #temp 
    INNER JOIN (SELECT Total, COUNT(*) AS Cnt FROM Temp_Table2010 GROUP BY Total) AS s
        ON Id = s.Total

Doing this:

WHERE total in (select id from #temp)

And then:

 INNER JOIN counts 
    ON t.id = counts.total

Is redundant.

The join solves the "total in (...)" requirement. Group on the key and then join.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MattInNY
  • 321
  • 3
  • 2
15

You can't use an aggregate in an UPDATE query, for starters - though you didn't include the error message in your original question, I suspect that's what it's telling you.

You'll need to calculate the aggregate before your update and store the results in a temp table, and then join to that table to do your update.

d219
  • 2,707
  • 5
  • 31
  • 36
SqlRyan
  • 33,116
  • 33
  • 114
  • 199