-3

I have 3 tables (inprod, outprod, Products), I want to update it every time a new row comes in. What I want to know is, how can I update it in a single query using Join? The columns affected are ProdName and stocksIn, exists in these 3 tables. Simply put, products = inprod - outprod.

Aldrin Roxas
  • 7
  • 4
  • 3
    To clarify the question, please add example tables and a desired result. It's almost impossible to write useful SQL without seeing the actual tables and a desired result to compare with. – Joachim Isaksson Feb 13 '16 at 08:46
  • I still don't have a query yet since I haven't tried Using Join but those are the tables that I'm using. Products where i'll update it everytime there are new inputs from inprod and outprod. I want to know the remaining number of stocks inprod.stocksIN - outprod.stocksIN then update Products.stocksIN. – Aldrin Roxas Feb 13 '16 at 08:57

1 Answers1

0

It is very simple.. Just because the database details is not mentioned in details I can give you an idea how you can update the database using pl/sql. 1. You can create a procedure which will store the rows changed with help of creating a trigger. The trigger will be triggered every time there is some changes in inprod table. 2. Once the rows changed are stored in the procedure already created you can update the related rows in the update table using the procedure already created. Let me know if you need a pl/sql code related to this. Or you need any help in creating procedure.

Auqib
  • 1
  • 3
  • OK, I didn't know the use of it before, I'm reading it right now. Can you create a trigger for me just so I can check it quickly? The tables are products, inprod and outprod. They all have prodname and stocksin as columns. I want the trigger to be activated every time there are inputs in inprod and outprod. (inprod.stocksin - outprod.stocksin) value will be stored to products table. – Aldrin Roxas Feb 13 '16 at 10:53
  • This is how you create a trigger.. depending upon the requirement the trigger can be used before or after the data is inserted, updated.... CREATE TRIGGER update_trigger BEFORE INSERT ON inprod FOR EACH ROW -- here you can use pl/sql code or what ever you want to do once the data is inserted in inprod table... BEGIN END; hope this link helps.. http://www.sqlservercentral.com/Forums/Topic561995-150-1.aspx – Auqib Feb 13 '16 at 11:41
  • @Auqib You're confusing me a bit here, the question is about MySQL but you're talking about PL/SQL (Oracle) and linking to SQLServerCentral (SQL Server). The idea is probably correct, but you may want to use terminology from the correct RDBMS. – Joachim Isaksson Feb 13 '16 at 16:25