Update Table using Computation from 2 other Tables
<p>Good Morning.
I want to ask some question but before that I want to explain what I'm currently have before asking the question.</p>

<p>I have 3 Tables and they are.</p>

<p><code>Table: Receiving</code></p>

<p><a href="https://i.stack.imgur.com/wxfWG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wxfWG.png" alt="enter image description here"></a></p>

<p><code>Table: GeneralInventory</code></p>

<p><a href="https://i.stack.imgur.com/2mXCV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2mXCV.png" alt="enter image description here"></a></p>

<p>and last one is <code>Table: item_master_list</code></p>

<p><a href="https://i.stack.imgur.com/SXrui.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SXrui.png" alt="enter image description here"></a></p>

<p>I have a program in VB.Net that will do the ff.</p>

<p>1.Based on <code>Table: Receiving</code> i will select an <code>RINo</code> and I will use the example on the image above(<code>RI00000001</code>)</p>

<p>2.After Selecting the <code>RI00000001</code> in <code>Table: Receiving</code> i will press the button 
<code>POST</code> and after that this is what my code does.</p>

<p>-The code will check if the Data from the Column <code>ItemCode</code> of <code>Table:Receiving</code> has the same Data in the Column <code>ItemCode</code> of <code>Table: GeneralInventory</code>.</p>

<p>-Now if the Comparison is True or there's a same Data from both Tables the Column <code>QtyPack</code> and <code>QtyStan</code> from <code>Table: Receiving</code> will be added/SUM in the Column <code>Qty</code> of <code>GeneralInventory</code></p>

<p>-Now if the Comparison is False or theres no comparison between two tables then the data will be inserted in <code>GeneralInventory</code>.</p>

<p>I hope you get it but if not here is the sample image.</p>

<p><a href="https://i.stack.imgur.com/sEeuC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sEeuC.png" alt="enter image description here"></a></p>

<p>and now my code for that procedure is this.</p>

<p>Code for finding.</p>

<pre><code>Select * 
From 
    GeneralInventory GI 
Inner Join 
    Receiving RE on GI.ItemCode = RE.ItemCode 
Where RE.RINo = 'MyValue'
</code></pre>

<p>Now if the code above is true then this is the code for Updating</p>

<pre><code>UPDATE GeneralInventory GI
INNER JOIN receiving RE ON GI.ItemCode = RE.ItemCode AND
GI.Qty = RE.QtyPack
SET GI.Qty = CAST(GI.Qty + RE.QtyPack + RE.QtyStan AS DECIMAL(6,2)) 
Where RE.RINo =  'MyValue'
</code></pre>

<p>or if it is false then this is the code for inserting.</p>

<pre><code>INSERT INTO GeneralInventory(ItemCode, Qty) 
SELECT RE.ItemCode, RE.QtyPack 
FROM 
    Receiving RE LEFT JOIN 
    GeneralInventory GI ON GI.ItemCode = RE.ItemCode AND 
        GI.Qty =CAST(RE.QtyPack + Re.QtyStan AS DECIMAL(6,2)) 
WHERE RE.RINo  = 'MyValue'
</code></pre>

<p>Now here is my Question and My Question is related to the <code>Item_Master_List</code> and here it is.</p>

<p>How can I achieve something like this? First before I will update the <code>Table:GeneralInventory</code> using the <code>Table: Receiving</code> I want to do some computation in <code>Table: item_master_list</code> what i mean is before i update or insert the data in <code>generalinventory</code> i will multiply it in <code>item_masterlist</code>.</p>

<p>Like find the same value in <code>item_master_list</code> then multiply it using the column of <code>QtyperUoM</code> using the value in <code>receiving</code> before saving it in <code>generalinventory</code></p>

<p>I hope you get it.</p>

<p>TYSM</p>