Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Unanswered: Update statement error for INNER JOIN

    Hi,

    I need to update Account table after getting the sum of total points from other table but i got error

    Code:
    select *,(redempTotalPoint + accTotalpoints) as Sumtotalpoint  from (
    (select accountid as accAccountid,Totalpoints as accTotalpoints
    from account )a
    INNER JOIN (
    select accountid as redempAccountid,sum(totalpoint) as redempTotalPoint from Redemption_PointsReimbursement20081006 group by accountid) b
    ON accAccountid = redempAccountid)
    order by accAccountid
    Update Statement:
    Code:
    update a set a.accTotalpoints = a.accTotalpoints + b.redempTotalPoint
    from (select accountid as accAccountid,Totalpoints as accTotalpoints
    from account )[a]
    INNER JOIN (
    select accountid as redempAccountid,sum(totalpoint) as redempTotalPoint from Redemption_PointsReimbursement20081006 group by accountid) [b]
    ON accAccountid = redempAccountid
    Error
    Code:
    Server: Msg 4421, Level 16, State 1, Line 1
    Derived table 'a' is not updatable because a column of the derived table is derived or constant.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Arguably you shouldn't be storing the totals, as you can calculate them on-the-fly whenever you need them.
    However, in terms of a working solution
    Code:
    UPDATE account
    SET    total_points = total_points + x.sum_of_total_points
    FROM   account a
     INNER
      JOIN (
            SELECT account_id
                 , Sum(total_points) As [sum_of_total_points]
            FROM   some_table
            GROUP
                BY account_id
           ) As [x]
        ON a.account_id = x.account_id
    but I strongly suggest you change your design.
    George
    Home | Blog

Posting Permissions

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