SQL Update with nested select and aggregate function

I'm trying to update one column of a table with the following query:

UPDATE Utable
Set Amt =
   (SELECT
        Sum(Amt)
    FROM Ptable
    WHERE ID IN
        (SELECT ID FROM UTable)
    GROUP BY
        ID,
        Col1,
        Col2,
        Col3
    )

I'd like the sum of AMT from Ptable to be placed in Utable  but I keep getting various error messages -- can I do this in one update statement -- How would I do it?
   
dkcoop03Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
i2mentalConnect With a Mentor Commented:
I'm going to assume that Ptable has multiple rows to each Utable ID.

UPDATE Utable
set utable.amt = (select sum(amt) from Ptable where ptable.id = utable.id)
0
 
dkcoop03Author Commented:
I have one question regarding this solution -- why doesn't  the nested select with the Sum(amt) require a group by clause?
0
 
crobledoCommented:
MS SQL:

Can I work a query as such

Max(Count(field1), Count(field2), Count(field3))  AS thisField

In other words can I easily nest aggregate function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.