Update Involving Subquery & Composite Key

Question

Update Involving Subquery & Composite Key

Dale Magnant

SSChasing Mays

Points: 647
More actions
August 27, 2007 at 1:28 pm

#180418

Hello,
I am trying to finish a query that posts issue slips against the OnHand field of an Inventory table. Basically the query should look at all the detail records in the issue slip detail table (issue_dx) and subtract the QuantityIssued field from the corresponding items in the inventory (inv) table. The query I had written is as follows;
UPDATE inv
SET inv.OnHand = inv.OnHand -
(SELECT sum(QuantityIssued)
FROM issue_dx
WHERE issue_dx.class = inv.class AND issue_dx.PartNumber = inv.PartNumber)
WHERE inv.class IN (SELECT class FROM issue_dx)
AND inv.PartNumber IN (SELECT PartNumber FROM issue_dx)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dale Magnant SSChasing Mays Points: 647 More actions · Answer 1

Sorry - I managed to post before completing the message,

The above query fails in the WHERE clause of the main query. The problem I am having is dealing with the composite key. If we had a single field key in the INV table (say, part#) then we could do this I assume;

...

WHERE inv.part# IN (SELECT part# FROM issue_dx)

and everything should work fine.

However, I am not seeing how to handle this expression with a composite key. If anyone could offer any suggestions I would be greatly appreciative.

Thanks,

Dale

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

what kind of error are you getting?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dale Magnant SSChasing Mays Points: 647 More actions · Answer 3

Matt,

Basically it is posting to too many records in the INV table.

In the final where clause,

WHERE inv.class IN (SELECT class FROM issue_dx) AND inv.PartNumber IN (SELECT PartNumber FROM issue_dx)

i need it to process the statement more as

...WHERE (inv.class AND inv.partnumber) IN ...

instead of looking for class and partnumber as separate entities in the issue slip detail table.

Dale

Carla Hepker Ten Centuries Points: 1295 More actions · Answer 4

I was able to use this SQL and it ran as is. I created a test table in Northwind and updated it from Order Details using the orderID and the ProductID as the composite key.

I had first inserted all the distinct combinations from Order Details into the test table.

I was able to achieve the same results with only the inner where clause, so I am not sure you need the second WHERE clause.

Then I deleted some records from the test table to see if that would cause an error, and it did not.

So, I guess I would try it without the second WHERE clause and see if you get the results you expect.

Regards,

Carla Hepker
Documentation: A Shaft of light into a coded world.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Try that one:

UPDATE inv

SET inv.OnHand = inv.OnHand -

(SELECT sum(QuantityIssued)

FROM issue_dx

WHERE issue_dx.class = inv.class AND issue_dx.PartNumber = inv.PartNumber)

WHERE

inv.PartNumber IN (SELECT PartNumber FROM issue_dx where inv.class=issue_dx.class )

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 6

Use join instead of subquery. I wrote this query but did not test it because I don't have your tables and data. Try to run the following query and let me know in case of any bugs:

update inv

set i.onhand = i.onhand - sum(d.QuantityIssued)

from inv i

join issue_dx d on i.class = d.class

and i.PartNumber = d.PartNumber

group by d.class, d.PartNumber

Carla Hepker Ten Centuries Points: 1295 More actions · Answer 7

I was going to suggest that also. But I had tried it with the WHERE clauses and it worked.

Code is much easier to read, in my opinion, using joins rather than WHERE clauses.

Regards,

Carla Hepker
Documentation: A Shaft of light into a coded world.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

I noticed that too (the join syntax), but for some reason the parser prefers the subquery, and flags the "group by" predicate in an UPDATE statement as an error (but will run it flawlessly....)

Something along the lines of "no Group By is allowed in an update statement"

Ahhh...Microsoft....<sigh>

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dale Magnant SSChasing Mays Points: 647 More actions · Answer 9

Hello,

Thank you all for all the feedback.

I had hoped to be able to try some of your suggestions this evening, but got derailed by other things.

I will have a go at things tomorrow and let you know how what I find.

Dale

Carla Hepker Ten Centuries Points: 1295 More actions · Answer 10

Now that I think about it, that is true. That is probably why I generally sum to a temp table and then update from there.

Regards,

Carla Hepker
Documentation: A Shaft of light into a coded world.

Dale Magnant SSChasing Mays Points: 647 More actions · Answer 11

Hello,

I had a chance to try out the suggestions. Here's what I found...

Matt - the query you suggested below works.

UPDATE

inv

SET inv.OnHand = inv.OnHand -

(SELECT sum(QuantityIssued)

FROM issue_dx

WHERE issue_dx.class = inv.class AND issue_dx.PartNumber = inv.PartNumber)

WHERE inv.PartNumber IN (SELECT PartNumber FROM issue_dx where inv.class=issue_dx.class )

I also did a little more experimenting with the original query and found that the following works also;

UPDATE

inv

SET inv.OnHand = inv.OnHand -

(SELECT sum(QuantityIssued)

FROM issue_dx

WHERE issue_dx.class = inv.class AND issue_dx.PartNumber = inv.PartNumber)

FROM inv, issue_dx

WHERE inv.Class = issue_dx.class AND inv.PartNumber = issue_dx.PartNumber

Mark - I tried the join version that you suggested and, as did the others, I seem to have run into a problem with the 'group by' clause. I want to experiment a little more with this as it just seems hard to believe there is not a way to do this with a join.

Carla - I tried a version of the original query without the second WHERE clause. It would not run against my tables, indicating that it was attempting to set the OnHand field to a NULL value (which the schema prohibits) I'm still not sure why it was giving this error - I cannot see any reason for it given the test data.

Question: Are there any standout books that any of you might recommend on SQL/T-SQL? I have several, but they all seem to give a few basic examples and never really offer the kind of examples/explanations that cover the real world stuff you have to deal with. I realize a lot of this comes through trial and error - I'd just like to eliminate a few of the trials.

Thanks again for all your help,

Dale