1

I have 2 tables, namely Products & Purchase:

products: id, productname, quantityleft.

purchase:id, productname, quantity.

PRODUCTS TABLE- will get all NEW products purchased. Meaning no same productname will exist in table prroducts. List of all unique Products

PURCHASE TABLE-list of all purchase products with unique Purchase_ID.

Here is my scenarios/problems:

  • If ever i want to add or insert values in PURCHASE, the table PRODUCTS will get all the values of purchase BUT only if productname in purchase is not existing in 'productname' in PRODUCTS.

  • If the productname purchase is already existed in productname products then the quantity in products will be added by the value of quantity in purchase having the same productname.

So far I have not met this situation before but maybe if I got this one it would be easy for me to code this. What I am thinking right now is to use the distinct but i know this is not the right thing to do. Can you suggest me the best and right way to achieve this one? Aside from I am really clueless, I can't googled it. Hope you could help me.

Handy Manny
  • 388
  • 1
  • 4
  • 20
  • This is called an "UPSERT". Here is a similar question which has a good answer if you have a primary key on the product table: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql – Tom Mar 14 '13 at 16:46
  • upsert is applicable in two tables too? – Handy Manny Mar 14 '13 at 17:09

1 Answers1

1

Well I will do at following way
Change the Scenario, 

products: id, productname.  
purchase: id, productname, quantity, prd_id.

Item purchased; check whether name exist in products
If not exist
- assign new product id
- Insert to products and purchase table.
Else if existing item purchased (check products id)
- Nothing to products table
- Update quantity+currentQty at purchase where prd_id = products.id

Surendra Jnawali
  • 3,190
  • 5
  • 28
  • 44