Returning sum in one table in one row from a series of records per user_ID

Question

Assuming Main Table has 10 columns with an Amount column, which must be updated form a second table, the DepositTable, with deposit column

1. MainTable id is titheID, and is unique, hence no multiple identical values allowed.

2. DepositTable can have the same titheID on several rows following different dates of deposit.

3. I have tried

   SELECT titheID, SUM(deposit)
   FROM DepositTable
   GROUP BY titheID
   

   and it worked on the DepositTable but how to update the MainTable Amount column with the results for each titheID, is my puzzle.

Because of the Unique state of the titheID in the MainTable, It throws Constraints Exception.

Please help

Answer 1

If I understood correctly you'rre looking for somethinglike:

UPDATE MT
SET MT.Amount = Sec.Total
FROM MaiTable MT INNER JOIN (SELECT titheID, SUM(deposit) Total FROM DepositTable GROUP BY titheID) Sec ON MT.titheID = Sec.titheID

Am I right?