March 19, 2009 at 9:20 am
Can we use GROUP BY clause in an UPDATE statement?
March 19, 2009 at 10:04 am
If you mean something like this:
Update table
Set columnA = Sum(columnB)
Group By
columnC
Then No.
Can you give some more information on what you are attempting to do?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2009 at 10:09 am
As of my knowledge, No you can not directly use GROUP by as you can not use aggregate functions in an UPDATE query.
But you can use a sub query which returns single value.
e.g.:
UPDATE Table1
SET Column1 =
(
SELECT Column2, SUM(Amount) FROM Table2
GROUP BY Column2
)X
WHERE x.Column2 = Column1
or
UPDATE Table1
SET Column1 =
(
SELECT Column2, SUM(Amount) FROM Table2
GROUP BY Column2
)X INNER JOIN Table1
ON x.Column2 = Column1
Hope this will help you..!
Regards,
Ramu
Ramu
No Dream Is Too Big....!
March 19, 2009 at 10:15 am
Jack Corbett (3/19/2009)
If you mean something like this:
Update table
Set columnA = Sum(columnB)
Group By
columnC
Then No.
However, if you use the UPDATE.. FROM syntax, it can be done just with a bit more typing
UPDATE tbl1
SET columnA = TotalB
FROM tbl1 INNER JOIN
(SELECT SUM(ColumnB) AS TotalB, ColumnC from tbl1 GROUP BY ColumnC) agg on tbl1.ColumnC = agg.ColumnC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2009 at 2:18 am
Thanks for all the replies.
Is it possible to use group by this way?
update table1
set totalvalue = sum(t2.column2)
from table1 t1
inner join table2 t2
on t2.column1 = t1.column1
group by t2.column3
March 20, 2009 at 7:29 am
gyessql (3/20/2009)
Is it possible to use group by this way?
Why don't you try it and see if SQL likes it or not? Probably faster than waiting for one of use to do the same.
Your code, pasted into a management studio query and parsed, returns the following:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'group'.
See the subqueries that I used for a way to do group by and aggregations in an update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2009 at 12:53 pm
Hi
As Gail already replied, sometimes it's faster to press CTRL+F5 than let us be the parser... 😉
For your problem try this:
WITH t2 (column1, column2) AS
(
SELECT t1.column1, SUM(t2.column2)
FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1
GROUP BY t1.column1
)
UPDATE t1 SET totalvalue = t2.column2
FROM table1 t1
INNER JOIN t2 ON t2.column1 = t1.column1
Greets
Flo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply