0

Good Afternoon. I have two Tables and they are

Table: GeneralInventory

enter image description here

and Table: Receiving

enter image description here

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) – Shadow Jul 07 '16 at 10:54

1 Answers1

-1

@
query = select * from GeneralInventory GI
inner join Receiving RE
on GI.ItemCode = RE.ItemCode
where RE.RINo = 'YOUR-VARIABLE'
if (@query) {
  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'
} else {
  NSERT 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'
}

Hi use this above query ..

rajsekar
  • 79
  • 1
  • 8
  • 1. What language / syntax does the above code snippet use? 2. You can do the above in a single sql statement if you have the appropriate index defined. – Shadow Jul 07 '16 at 11:09
  • @ query = select * from GeneralInventory GI inner join Receiving RE on GI.ItemCode = RE.ItemCode where RE.RINo = 'YOUR-VARIABLE' if (@query) { 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' } else { 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' } – rajsekar Jul 07 '16 at 11:34