Inner Join for a parent/child relationship?

Databases
1

Hi all-
I have an application with a parent/child relationship, both records are in the same table. I’m trying to write a MSSQL script to run nightly to make an update to the cost of the child. So my table is something like this (simple form):

ID| UPC | DESCRIPTION | COST | PRICE | PARENT_ID | PARENT QTY
1 | 123 | 6PK BEER | 6.00 | 8.00 | <NULL> | <NULL>
2 | 345 | SINGLE BEER | 1.00 | 2.00 | 1 | 6

My application is setup to update the cost of the master pack (the 6pk in this case) but the cost update doesn’t roll down to the child. The formula would be Cost = 6/6, but I’m struggling with how to write the script.

I was thinking an inner join but can’t seem to formulate the syntax. Any ideas?

Thanks much.

2

Assuming that your table is called ‘tblStock’, this should do it;


UPDATE A
     SET A.COST = B.COST/A.PARENT_QTY
     FROM tblStock AS A
             INNER JOIN tblStock AS B ON A.PARENT_ID = B.ID
3

Works perfectly. Thanks much!

4

I guess you should include a check to make sure you’re not trying to divide by zero, just to be on the safe side.

5

good call.