Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    24

    Unanswered: Unable to combined UPDATE with GROUP BY? How to work around?

    Hello,

    I am trying to update a field in a temporary table, as shown below.
    Get an error: Incorrect syntax near the keyword 'GROUP'.


    UPDATE #tempDEALS
    SET
    termination_fee = SUM(F.fee)
    FROM
    #tempDEALS TD,
    fees F
    WHERE
    TD.combinedID = F.combinedID
    AND F.fee_type = 9 -- TERMINATION
    GROUP BY
    F.combinedID


    I take it an UPDATE with a GROUP BY is not permitted.
    Any suggestions on how I can somehow otherwise accomplish this?

    Thanks, chasse

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    GROUP BY is for formatting only. If you leave it out of the UPDATE-statement, the same records would be updated.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Aug 2008
    Posts
    24
    Thanks for the reply Martijnvs.
    But I don't think that's true.
    I am looking to sum up the fees for the records that have shared "combinedID's".
    The GROUP BY is necessary for that.

    I found a work-around:
    Use a SELECT INTO (with GROUP BY) to create a temporary table,
    followed by an UPDATE to the intended destination table #tempDEALS:

    SELECT
    TD.combinedID,
    termination_fee = SUM(F.fee)
    INTO
    #tempTERMFEES
    FROM
    #tempDEALS TD,
    fees F
    WHERE
    TD.combinedID = F.combinedID
    AND F.fee_type = 9 -- TERMINATION
    GROUP BY
    TD.combinedID

    UPDATE #tempDEALS
    SET
    TD.termination_fee = TF.termination_fee
    FROM
    #tempDEALS TD,
    #tempTERMFEES TF
    WHERE
    TD.combinedID = TF.combinedID

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No need for another temp table
    Code:
    UPDATE #tempDEALS 
    SET
    termination_fee = 
    (select SUM(F.fee) 
     FROM
     fees F
     WHERE
     TD.combinedID = F.combinedID
     AND F.fee_type = 9 -- TERMINATION
     GROUP BY F.combinedID)
    FROM
    #tempDEALS TD
    I don't know the rest of your process but do you really need the temp table #tempDEALS

  5. #5
    Join Date
    Aug 2008
    Posts
    24
    Thanks pdreyer!!
    That is the ideal answer.

    And yes, #tempDEALS is needed.
    However, it is now the sole temp table in a 500 line stored proc,
    which is solely dedicated to populating it properly,
    and then selecting it's content.

    Thank you - I learned something very useful.

Posting Permissions

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