Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
Update Statement

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote 
    ;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