How to process a Master Table??

Question

hi, every body,

Please advice for the following task in C#.net

I am having one master table and one transaction table. The master table consists the following columns..& Data..

Code description amount code

101 abc 150 D (Debit)

102 def 50 C (Credit)

103 hfh 200 D (Debit)

the Transaction table contains the following Columns and Data

Code amount code

101 150 D (Debit)

101 60 C (Credit)

102 50 C (Credit)

102 200 D (Debit)

103 200 D (Debit)

103 100 D (Debit)

Now I have to update the master table with tranaction table data.. I have tried this at my level best and i am not getting the exact idea how to solve this. So, let me know how to solve this task in C#.net and sql server database.

Thank u very much in advance.. expecting a positive and quick responsed regarding this post.

Thanks & Regards

Datt JVN

Answer 1

Not sure what you are really wanting to do, but here is my attempt.

--ANSI SQL
UPDATE
  tbl_Master
SET
  amount = (
             SELECT
              SUM(amount)
             FROM
              tbl_tran
             WHERE
              code = 101
           )
WHERE
  code = 101

--T-SQL
UPDATE
  tm
SET
  amount = summd.amount
FROM
  tbl_Master tm
  JOIN (
         SELECT
          tm.Code
         ,amount = SUM(tt.Amount)
         FROM
          tbl_Master tm
          JOIN tbl_tran tt
            ON tm.Code = tt.Code
         GROUP BY
          tm.Code
       ) summd
    ON tm.code = summd.Code