Nyx Assasin Nyx Assasin - 4 months ago 9
SQL Question

Update Table using Computation from 2 other Tables

Good Morning.
I want to ask some question but before that I want to explain what I'm currently have before asking the question.

I have 3 Tables and they are.

Table: Receiving


enter image description here

Table: GeneralInventory


enter image description here

and last one is
Table: item_master_list


enter image description here

I have a program in VB.Net that will do the ff.

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

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

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

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


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

I hope you get it but if not here is the sample image.

enter image description here

and now my code for that procedure is this.

Code for finding.

Select *
From
GeneralInventory GI
Inner Join
Receiving RE on GI.ItemCode = RE.ItemCode
Where RE.RINo = 'MyValue'


Now if the code above is true then this is the code for Updating

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'


or if it is false then this is the code for inserting.

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'


Now here is my Question and My Question is related to the
Item_Master_List
and here it is.

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

Like find the same value in
item_master_list
then multiply it using the column of
QtyperUoM
using the value in
receiving
before saving it in
generalinventory


I hope you get it.

TYSM

Answer
UPDATE generalinventory gi
set gi.qty = (SELECT (r.QtyPack * r.QtyperUoM + r.QtySan) 
           from receiving r, item_master_list ml
           where r.itemCode = ml.itemCode
           and   t.itemCode = gi.itemCode)
Comments