How to rewrite query

I have this as part of SP
DECLARE @.StockId bigint
SELECT @.StockId=Reservation.StockId
FROM Reservation INNER JOIN
Stock ON Reservation.StockId = Stock.StockId
WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
@.WarehouseId)
UPDATE Reservation
SET Amount=Amount-@.Amount
WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
I know there it could be done in one statement. How?
Thanks.Shimon Sim wrote:
> I have this as part of SP
> DECLARE @.StockId bigint
> SELECT @.StockId=Reservation.StockId
> FROM Reservation INNER JOIN
> Stock ON Reservation.StockId = Stock.StockId
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
> @.WarehouseId)
> UPDATE Reservation
> SET Amount=Amount-@.Amount
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
> I know there it could be done in one statement. How?
> Thanks.
>
This should get you close:
UPDATE Reservation
SET Amount = Amount - @.Amount
FROM Reservation Reservation
INNER JOIN Stock Stock
ON Reservation.StockID = Stock.StockID
WHERE Reservation.OrderItemID = @.OrderItemID
AND Stock.HolderID = @.WarehouseID
I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into
the stored procedure?|||Yes. They are parameters.
Thank you.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OpFYPvAlGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Shimon Sim wrote:
> This should get you close:
> UPDATE Reservation
> SET Amount = Amount - @.Amount
> FROM Reservation Reservation
> INNER JOIN Stock Stock
> ON Reservation.StockID = Stock.StockID
> WHERE Reservation.OrderItemID = @.OrderItemID
> AND Stock.HolderID = @.WarehouseID
> I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into the
> stored procedure?