update from a select in a single statement
-
Hi everybody,
I have 2 tables: one is the products table where each product has his own stock quantity. And another one the orderitem table where the orders are stored. I would like every hour to update the stock quantity from the products table: to deduct the quantity from orderitem table from the stock quantity from products table in a single select statement.
Is this possible, or do I have to use cursors?
PS. I know that the business logic is not the most proficient, but this is how is required (to update every hour and not real time).
Thanks a lot,
-
I do not understand if you want to update one or both tables at the same time,
but the general "joined update" looks something like:
UPDATE A
SET A.Column1 = B.StockQTY
FROM OrderItem A
JOIN Product B
ON A.ProductID = B.ProductID
-- For aggregates....
UPDATE A
SET A.Column1 = B.ColumnX
FROM OrderItem A
JOIN (
SELECT ProductID,
SUM(StockQTY) as StockQTY
From Product
GROUP BY ProductID) B
ON A.ProductID = B.ProductID
-- I have never tried both A & B....
UPDATE A
SET A.Column1 = B.StockQTY,
B.ColumnX = A. ColumnX
FROM OrderItem A
JOIN Product B
ON A.ProductID = B.ProductID
Once you understand the BITs, all the pieces come together
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply