Updating 2 tables simultaneously using SQL

[TallGuyinIT]

New to SQL and I am really struggling with some of the concepts. I am trying to calculate how many orders have inventory available. I have 2 temp tables;

table 1 has columns - order#, SKU, qty ordered, qty available.

table 2 has columns - SKU, qty on hand

I am trying to write a loop that will scroll through table #1 one record at a time and update qty available with qty ordered or 0 based on table #2 qty on hand. After determining the qty available, that number needs to be subtracted from qty on hand in table #2 so that inventory is not over committed.

I have searched this forum and googled updating 2 tables simultaneously to no avail. Can anyone offer some help?

Thank you

 

[Crowley16]

sql is designed for set operations, so there's no real need for looping.

what you require can be achieved by a set of individual update statements...

something like:

update t1
set qtyAvail = qtyOnHand
from t2
where t1.sku = t2.sku

update t2
set qtyOnHand = qtyOnHand - qtyOrdered
from t1
where t1.sku = t2.sku

...

you can wrap all of this in a sproc with a transaction if you feel they have to all be done at once...

--------------------
Procrastinate Now!

 

[SQLSister]

Please do yourself a favor and remove the concept of looping through records from your thought processes. When SQL server loops it runs the same query multiple times. this takes exponentially longer than running the query against the entire set of data. Look in BOL for examples how to run insert statements using the select statmenet rather than the values clause and updates and deletes using joins. Better to learn the correct way from the start.

Make sure to do this as Crowley said in a transaction. That way if for any reason the first fails the second update will not happen and if the second update fails the first will be rolled back. Make sure you write it so that you perform the rollback or commit depending on the success of the individual actions.


"NOTHING is more important in a database than integrity." ESquared