Solved

SQL Update 1 tables columns from a different tables columns

Posted on 2007-11-28
4
213 Views
Last Modified: 2010-03-20
Looking for some help with trying to write a stored procedure that will update 2 columns with values from another tables columns. I have a table that holds inventory items for both wherehouse locations. A user needs to look at what is in Wherehouse A and then builds a list of items that are needed at Wherehouse B. Once the user has done this an order is generated to be filled and shipped out. When wherehouse B recieves the order they check the qty and then updates the transfer request saying whether or not all items were received. My problem now is that I am needing to update the inventory table to show that items from wherehouse A are now in stock in wherehouse B.
I have no problems updating the transfer table when the items are recieved but now I have a problem updating the inventory table to show updated qtys and that the location has changed. What is the best way to do this?
0
Comment
Question by:digital_slavery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20372244
update a
set col1 = b.col1, col2 = b.col2
from WarehouseATbl a
 inner join WarehouseBTbl b on ... condition
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372269
can you describe what sort of tables and fields you have, and what sort of procedure you are after? i.e.

inventory: location, stockcode, current_qty
sp: input - locationfrom, locationto, stockcode, qty

something like

create procedure stockmoved (  @locationfrom int, @locationto int, @stockcode int, @qty int )
as
update inventory set qty = qty - @qty where locationid = @locationfrom and stockcode = @stockcode
update inventory set qty = qty + @qty where locationid = @locationto and stockcode = @stockcode
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20953231
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question