none
Update Statement

    Question

  • to update a field in an table with a sum from itself. Can't quite get the syntax down.. Here is what I have.. What am I missing.

    Update EventData
    Set DivTotal = (Select EventKey, SUM(EventPoints) as DivTotal
    From EventData
    Where eventcode = 'F'
    Group by Eventkey)
    Wednesday, August 20, 2014 12:23 PM

Answers

  • Update e
    Set DivTotal = t.DivTotal from EventData ed
    join (Select EventKey, SUM(EventPoints) as DivTotal
    From EventData
    Where eventcode = 'F'
    Group by Eventkey) t
    on ed.EventKey = t.EventKey


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed as answer by Avijit Swain Wednesday, August 20, 2014 12:26 PM
    • Marked as answer by PAPIAS53 Wednesday, August 20, 2014 12:33 PM
    Wednesday, August 20, 2014 12:26 PM

All replies

  • Update e
    Set DivTotal = t.DivTotal from EventData ed
    join (Select EventKey, SUM(EventPoints) as DivTotal
    From EventData
    Where eventcode = 'F'
    Group by Eventkey) t
    on ed.EventKey = t.EventKey


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed as answer by Avijit Swain Wednesday, August 20, 2014 12:26 PM
    • Marked as answer by PAPIAS53 Wednesday, August 20, 2014 12:33 PM
    Wednesday, August 20, 2014 12:26 PM
  • ;With mycte as (select EventKey, SUM(EventPoints) as DivTotal
    From EventData
    Where eventcode = 'F'
    group by EventKey)
    
    Merge EventData t
    using mycte s on t.EventKey=s.EventKey
    When matched then
    UPDATE Set DivTotal=s.DivTotal;

    Wednesday, August 20, 2014 12:43 PM
    Moderator