0

Good Afternoon. I have two Tables and they are

Table: GeneralInventory

  WareHouseID | ItemCode | Qty
  Null          Null       Null

Note: as of now it is empty

and Table: Receiving

   RINo     |  ItemCode | ReceivedQty
    RI805ZFJ   FBMURG5WR   1.00
    RI467RGR   FBMURG5WR   1.00

As you can see this formats on Table are most likely you will see in Inventory System, My Table GeneralInventory is the Main Inventory where all Items that you will received will transfer here and that Table is Receiving as what you see in the table Receiving there 2 Data and they are.

enter image description here

Please be focused on the RINo field they are Different right? now here is what I will do and also my Question (I will convert my Question in Step by Step procedure of the Program)

  1. The Program will Select 1(One) Data from Table Receiving

  2. I will click a button named Post and the Program will do the Following

    -Check if the ItemCode of the Selected RINo already Exist in the Table GeneralInventory and If the ItemCode is already in there then ReceivedQty of table Receiving and Qty of Table GeneralInventory will sum up or else add the Data.

I hope you get my point but if not I will show you further Example.

enter image description here

In General: If the Data Exist then Sum it Up or Else add the New Data.

I am using this Code.

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE LEFT JOIN
     GeneralInventory GI
ON   GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
WHERE RE.RINo = 'Data of RINo'

UPDATE GeneralInventory GI
INNER JOIN receiving RE
    ON GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
SET GI.Qty = GI.Qty + RE.ReceivedQty RE.RINo = 'Data of RINo'

TY for future Help

0

You can use INSERT ON DUPLICATE KEY UPDATE. For this it is necessary to have a unique index on GeneralInventory.ItemCode, so that duplicates can be detected!

Your statement would look like the following:

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE
WHERE RE.RINo = 'Data of RINo'
ON DUPLICATE KEY UPDATE
SET Qty = Qty + VALUES(Qty);

A quote from the above linked manual page to explain the VALUES() function:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.