?
Solved

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

Posted on 2008-10-14
4
Medium Priority
?
289 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 2000 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 93

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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