Update one table with SUM from another

Question

I'm trying to update a table variable with a sum from another table. Because of the way the tables are set up, this requires a couple JOINS and I can't seem to figure out what gets joined and how in order to get the desired result set.

Here are the tables I'm working with:

CCR (variable declared as TABLE):
AttyID   Name   AttyState   CCspent

PH:
number   attorneyID

LL:
AccountID   DebitAmt   ItemDate

I have the AttyID, Name, and AttyState in CCR already populated, now I need to UPDATE CCspent. I have to get the SUM of LL.DebitAmt where LL.ItemDate is in a specific date range.

The PH table does not have AccountID, and the LL table does not have AttorneyID.

I found Update table with SUM from another table but there weren't any joins so it wasn't very helpful for my situation.

As a test query, I ran the following:

SELECT SUM(LL.DebitAmt)
FROM Legal_Ledger LL
    INNER JOIN payhistory PH
        ON LL.AccountID = PH.number
WHERE LL.ItemDate BETWEEN '2014-07-01' AND '2014-07-31'
    AND PH.AttorneyID = 6

and it produced the expected result for Attorney 6, so I know that logic works.

However, when I use:

UPDATE @IA_CourtCostReport 
SET CCspent = 
    COALESCE
    (
    (
    SELECT SUM(LL.DebitAmt)
FROM Legal_Ledger AS LL
INNER JOIN master
    ON LL.AccountID = master.number
INNER JOIN @IA_CourtCostReport CCR
    ON master.attorneyID = CCR.AttyID
WHERE LL.ItemDate BETWEEN @StartDate and @EndDate
    AND master.AttorneyID > 0
    )
    ,0
    ) * -1

then it updates all rows in CCR with the grand total of all costs for all attorneys in the date range. I need individual totals by Attorney.

You need to add the attorneyID to your select and add a group by. — Andrew, Aug 04 '14 at 21:22

score 1 · Answer 1 · answered Aug 04 '14 at 21:35

Use update with join:

UPDATE ccr
    set ccspent = llph.sumdebitamt
FROM @IA_CourtCostReport ccr join
     (SELECT PH.AttorneyID, SUM(LL.DebitAmt) as sumdebitamt
      FROM Legal_Ledger LL INNER JOIN
           payhistory PH
           ON LL.AccountID = PH.number
      WHERE LL.ItemDate BETWEEN '2014-07-01' AND '2014-07-31'
      GROUP BY PH.AttorneyID
     ) llph
     ON llph.AttorneyID = ccr.AttyId;

score 0 · Answer 2 · answered Aug 04 '14 at 21:29

You'll need to use this for deriving your sums:

SELECT 
master.attorneyID,
SUM(LL.DebitAmt)
FROM Legal_Ledger AS LL
INNER JOIN master
    ON LL.AccountID = master.number
INNER JOIN @IA_CourtCostReport CCR
    ON master.attorneyID = CCR.AttyID
WHERE LL.ItemDate BETWEEN @StartDate and @EndDate
    AND master.AttorneyID > 0
GROUP BY master.AttorneyID

score 0 · Answer 3 · answered Aug 04 '14 at 21:31

You need to a reference to the table you are updating in the query you are using to set the total.

You should just be able to change

Update @IA_CourtCostReport

to

Update CCR

Also, why are you using a table variable? #temp tables offer superior performance in most instances.

score 0 · Answer 4 · answered Aug 04 '14 at 22:04

It's a bit complicated for me to understand...but if you want the total that corresponds to a specific attorney, the I'd suggest that you "group by AttorneyID".

UPDATE CourtCostReport set CCSpent=
  select my_sum from(
    select AttorneyID as id, sum(...) as my_sum from ... group by AttorneyID
  )
where AttorneyID=id

Update one table with SUM from another

4 Answers4