March 1, 2007 at 3:50 pm
Hello,
I am trying to write an update query for the following situation...
I have an application that generates warehouse issue slips. Each issue slip will have one or more line items. The cost of the line items on each issue slip need to be posted to a facility expense table (fac_exp). All the items on a single issue slip will be charged to one facility. The facility expenses table has one row per facility.
The issue slips are stored in a standard master/detail (issue_mx/issue_dx) table arrangement where the facility code to be charged against is in the header table. In the detail table, for each line item, is a quantity and price field. These two tables are linked by a slip# field.
I have come up with the query below which works - with one exception. If the issue slip has more than one line item, only one line item posts. I am fairly new to this and don't understand why the query is behaving this way. I would greatly appreciate any guidance anyone might offer.
Thank You in advance.
update fac_exp
set fac_exp.ytd_maintenance = fac_exp.ytd_maintenance + (issue_dx.quantity_issued * issue_dx.price)
from
fac_exp inner join issue_mx on fac_exp.facility_code = issue_mx.facility_code
inner join issue_dx on issue_mx.slip# = issue_dx.slip#
March 2, 2007 at 2:05 am
Dale
You haven't provided table DDL or sample data, so I can only guess. You should write your query as a select statement first and then convert it to update if it returns the correct data. I think you're looking for something like this:
select d.slip#, f.ytd_maintenance + SUM(issue_dx.quantity_issued * issue_dx.price) as NewTotal
from fac_exp f inner join issue_mx m
on f.facility_code = m.facility_code
inner join issue_dx d on m.slip# = d.slip#
group by d.slip#
John
March 2, 2007 at 1:43 pm
John,
Thanks for the response. I did some further rummaging around the SQL Server online help and ended up doing a complete rewrite of my query. I came up with the following query which through a brief bit of testing, seems to work fine. Coming from a background of procedural programming, this looks ugly - I'm amazed something like this actually works.
The key to getting on the right road seemed to lie with a statement I found in the on-line help which said "...a single UPDATE statement never updates the same row two times." With my original query, whenever I had an issue slip with multiple line items, I was assuming I would get multiple posts to the facility expense table.
Anyhow, if there is a more elegant way to write this query, I am certainly open to suggestion.
Also - how does one get around the double line spacing when making posts?
UPDATE fac_exp
SET
fac_exp.ytd_maintenance = fac_exp.ytd_maintenance +
(SELECT sum(issue_dx.quantity_issued * issue_dx.price)
FROM issue_dx
WHERE issue_dx.slip# =
(SELECT issue_mx.slip#
FROM issue_mx
WHERE issue_mx.facility_code = fac_exp.facility_code)
GROUP BY issue_dx.slip#)
WHERE
fac_exp.facility_code IN
(SELECT facility_code
FROM issue_mx);
March 2, 2007 at 2:26 pm
It helps to break the problem down into chunks. First, you need total amount summed at the facility_code level. This query accomplishes that:
Select m.facility_code, Sum(d.quantity_issued * d.Price) As TotalAmount
From issue_mx as m
Inner Join issue_dx As d
On (d.[slip#] = m.[slip#])
Group By m.facility_code
Once you have that tested and confirmed to be correct, you can re-use that query as a derived table, to drive the UPDATE. Using the select above in a derived table gives this update statement:
Update f
Set ytd_maintenance = ytd_maintenance + dt.TotalAmount
From fac_exp As f -- Table being updated
Inner Join
(
-- derived table, same as query above
Select m.facility_code, Sum(d.quantity_issued * d.Price) As TotalAmount
From issue_mx as m
Inner Join issue_dx As d
On (d.[slip#] = m.[slip#])
Group By m.facility_code
) dt
On (dt.facility_code = f.facility_code)
FYI - to avoid double line spacing in a forum posting, hold Shift-Enter to create a newline.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply