Solved

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

Posted on 2008-10-14
4
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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