i have 2 tables in mysql , are:
as see on image above the first 4 rows of field item code of tables general inventory , purchase order same , 5th row in table purchase order not in table general inventory. question how can achieve this? (image below)
as see on image above data field quantity of tables general inventory , purchase order has been sum because there preceding data under field item code same , last 1 has no equal value in general inventory table has been added.
let me shorten question.
how can sum data field quantity of purchase order in general quantity if there data in item code field same else add new data if data purchase order doesn't exist in general inventory.
i tried code.
update generalinventory gi inner join purchaseorder po on gi.itemcode = po.itemcode , gi.`description` = po.`description` , gi.quantity = po.quantity set gi.quantity = gi.quantity + po.quantity and output this
now tried second code
insert generalinventory (itemcode, `description`, quantity) select po.itemcode, po.`description`, po.quantity purchaseorder po left join generalinventory gi on gi.itemcode = po.itemcode , gi.`description` = po.`description` , gi.quantity = po.quantity gi.itemcode null the output this.
the data has been repeated instead the item 5 added.
first using left join can inserted not matching rows generalinventory table
insert generalinventory (itemcode, `description`, quantity) select po.itemcode, po.`description`, po.quantity purchaseorder po left join generalinventory gi on gi.itemcode = po.itemcode , gi.`description` = po.`description` , gi.quantity = po.quantity gi.itemcode null then can update generalinventory's quantity value joining tables
update generalinventory gi inner join purchaseorder po on gi.itemcode = po.itemcode , gi.`description` = po.`description` , gi.quantity = po.quantity set gi.quantity = gi.quantity + po.quantity 