Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: UPDATE using aggregate and inner join

    I want the sum of an amount to update another table using inner join.

    The following query -

    UPDATE A
    SET A.[Col3] = SUM(B.[Col3])
    FROM A
    INNER JOIN B
    ON A.C1 = B.C1
    AND A.C2 = B.C2

    Gives me an error saying I cannot use SET with aggregate Column.

    How do I perform this aggregate update?

    Thanks,

    Vivek

  2. #2
    Join Date
    Aug 2004
    Posts
    10
    one way is to use a subquery. try this:

    update a set a.col3 = b.col3 from a inner join (select sum(col3) as col3 , c1, c2 from b group by c1, c2) b on a.c1 = b.c1 and a.c2 = b.c2

    hth,
    Cristian Babu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •