Is it possible to update records in one SELECT with the detail totals from another file

Question

I have a trigger created on an invoice header file. It creates a PPAY table row, however the total amount is not known at the time of the trigger. The amount needed to populate the PPAY amount is the total of all the associated invoice detail records (which are not written out at the time of the header insert trigger).

The Header record insert trigger creates a PPAY record with a NULL amount. The total amounts are to be gathered from the MRRECVD table, where the CONTROL# of both the PPAY and MRRECVD have to match.

The following does not work:

-- Add the amount to the PPAY items in one sentence
UPDATE [PositivePay].[dbo].[PPAY] 
    SET AMOUNT = (
        SELECT SUM(DETAIL_AMOUNT) 
        FROM [TMP].[dbo].[MRRECVD] d
        WHERE d.CONTROL = CONTROL
        GROUP BY d.CONTROL
        )
WHERE AMOUNT IS NULL

I get this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My understanding is that to get around that, I should reduce my subquery so that it only returns one result, which is the whole idea of SUM().

I should also mention that the common factor between the PPAY and MRRECVD tables is the CONTROL column, whereas there can be multiple rows in the PPAY table differentiated by a CONTROL_SEQ (1,2,3,etc).

Is this even possible to do?

Answer 1

Including GROUP BY d.CONTROL is not necessary although I cannot see why it would give you more than one value because you are supposed to only have one type of control value given your where clause.

Try removing the GROUP BY clause to see if you get a single sum value.

Answer 2

It works! It should be stressed that the GROUP BY clause is necessary because of the aggregate SUM()

UPDATE p
    SET p.AMOUNT = d.sum_amt
FROM [PositivePay].[dbo].[PPAY] p
INNER JOIN
    (
        SELECT  CONTROL, sum(d.DETAIL_AMOUNT) as sum_amt
        FROM [TMP].[dbo].[MRRECVD] d
        GROUP BY CONTROL
    ) AS d
    ON p.CONTROL = d.CONTROL
WHERE p.AMOUNT IS NULL