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?