Solved

SQL stored proceedure to sum detail records and update a master record

Posted on 2008-10-14
4
254 Views
Last Modified: 2012-05-05
I have a table with transaction details in it. I've also got a table with a master record in it that contains a field for original amount and amount remaining.. I would like to write a stored procedure that will sum the detail records, subtract them from the original amount, and update the master record.

Below is a sample of what I'd like the two tables to look like. Any help would be appreciated.

tblTransactions.

BLOCKID QTY
1                10
1                20
2                5
2                2

tblMaster

BLOCKID  ORIGAMT REMAMT
1               50                 20
2               50                 43



0
Comment
Question by:iansmcg
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22716209
you mean:
UPDATE m
    SET REMAMT = m.ORIGAMT - ISNULL( t.qty , 0 )
  FROM tblMaster m 
  LEFT JOIN ( SELECT BLOCKID, SUM(QTY) qty FROM tblTransactions group by blockid ) t

Open in new window

0
 

Author Comment

by:iansmcg
ID: 22716276
Well when I attempt to run the following (updated for case and with proper field and table names)

UPDATE m
    SET REAMT = m.ORGAMT - ISNULL(t.QTY , 0 )
  FROM PPtblBlock m
  LEFT JOIN (SELECT BLOCKID, SUM(QTY) QTY FROM PPtblTransactions group by BLOCKID) t

I get:

Server: Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 't'.

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22716313
sorry, the last line got "cut"
UPDATE m
    SET REAMT = m.ORGAMT - ISNULL(t.QTY , 0 )
  FROM PPtblBlock m
  LEFT JOIN (SELECT BLOCKID, SUM(QTY) QTY FROM PPtblTransactions group by BLOCKID) t
  ON t.BLOCKID = m.BLOCKID

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22716322
Hello iansmcg,

You have to specify which columns the tables are joined on, e.g.:

UPDATE m
    SET REAMT = m.ORGAMT - ISNULL(t.QTY , 0 )
  FROM PPtblBlock m
  LEFT JOIN (SELECT BLOCKID, SUM(QTY) QTY FROM PPtblTransactions group by BLOCKID) t ON m.SomeColumn = t.BLOCKID

Regards,

Patrick
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Replace Carriage Returns for Excel Exports 17 57
Add different cell to otherwise similiar row 4 45
Oracle - Query link database loop 8 40
string fuctions 4 28
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question