Solved

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

Posted on 2008-10-14
4
255 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL - format decimal in a string 5 51
SQL Select in Access 2003 3 43
SQL Recursion 6 34
denied execute as 13 31
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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